Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
suvechha_b
Creator III
Creator III

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

Hi Folks ,

Please kindly assist me in writing the below set analysis .

Say for eg ,

Capture.PNG

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.

Capture.PNG

Thanks,

2 Replies
avkeep01
Partner
Partner

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
Partner
Partner

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)