Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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)
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)