How to write month between current and previous month in set analysis expression . ?

[suvechha_b]

Hi Folks ,

Please kindly assist me in writing the below set analysis .

Say for eg ,

the same deal_id has got another entry for month of april with dealstate_id as 33 . like below

deal_id         date_updated          dealstate_id        dealgroup_id        ..............   customerIDNumber                  dicAmount

13308339   2018-04-20               33                       4163846                                 9202026674088                       2672.02

So, at that time my expression is not working , How can I check month between current and previous month.

sample model attached. Please see the DIC Report where I am trying to compare month field between current and previous month

eg expression ,

=if(LatestForPodium ='1' and LatestForState ='1' and bank='Wesbank',sum({<bank={'Wesbank'},month={">=$(=Month(AddMonths(Today(), -1))) <=$(=Month(Today()))"}> } DISTINCT if(LatestForPodium ='1' and LatestForState ='1',dicAmount)),sum({<bank={'Wesbank'},month={">=$(=Month(AddMonths(Today(), -1))) <=$(=Month(Today()))"}> } DISTINCT if(LatestForPodium ='1' or LatestForState ='1',dicAmount)))

Its is only working when I am selecting both April and May on Main DIC . But , my requirement is , if it can work when only selecting May Month - trying to implement on DIC Report.

Screenshot attached.

Thanks,

[avkeep01]

Hi Suvechha,

The problem is that the field month isn't considered a numeric value in your set analysis.

Make an extra field in the script NUM(MONTH(Date)) AS month_num, then use num() in your set analysis around the months. Also don't forget to add month= to ignore the month selections,

btw, your expression looks over complicated. try:

sum(DISTINCT  {<bank={"Wesbank"},month_num={">=$(=NUM(Month(AddMonths(Today(), -1)))) <=$(=NUM(Month(Today())))"},month=, LatestForPodium ={1},LatestForState ={1} > } dicAmount)

Also when testing this expression drop the different selections just to be sure.

sum({<month_num={">=$(=NUM(Month(AddMonths(Today(), -1)))) <=$(=NUM(Month(Today())))"},month=> } dicAmount)

[avkeep01]

I created a new field in the top table, the field is called test. it uses my last expression:

sum({<monthnum={">=$(=NUM(Month(AddMonths(Today(), -1)))) <=$(=NUM(Month(Today())))"},month=> } dicAmount)