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

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

suvechha_b — Wed, 25 Nov 2020 16:16:04 GMT

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,

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

avkeep01 — Thu, 31 May 2018 07:46:00 GMT

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)

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

avkeep01 — Thu, 31 May 2018 07:50:57 GMT

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)