Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
neena123
Partner
Partner

Set Analysis issue. Need to create a report that pulls for the current month(June 2016), a list of customers whose last purchase was May 31, 2015 or earlier.

So basically the report needs to go back 12 months to 24 months of the last purchases made by the customer. So the full span from last purchase is June 1, 2013 to May 31, 2015. So, the report would look at all customers in our database and if their last purchase was 12-36 months ago, pulled into a report with no purchases in the current year. I am having difficulty understanding Set Analysis. I have looked at many articles but I can't seem to get my expressions to work. Any thoughts on why my expressions below aren't working?

=sum({$<[Fiscal Month]={"<$(=Month(Today(),-23))"}>}[Sales Amount])

=sum({$<[Fiscal Month]={"<$(=Month(Today(),-11))"}>}[Sales Amount])

5 Replies
sunny_talwar

May be this:

=Sum({$<[Fiscal Month]={"<$(=Month(AddMonths(Today(),-23)))"}>}[Sales Amount])

=Sum({$<[Fiscal Month]={"<$(=Month(AddMonths(Today(),-11)))"}>}[Sales Amount])

neena123
Partner
Partner
Author

Thanks for the quick reply! But it did not work.

Not applicable

Hi,


Try creating a master calender in your script based on the the min and max date of your date range. From there, incorporate the "year" field in your set analysis. This should allow you to now do rolling 12, 24, etc. calculations. 


Hope this helps. 


Regards,

Pravesh

neena123
Partner
Partner
Author

How is that created though? I do have a calendar but how do I use it to create rolling calculations?

Not applicable

the following expression can be used:

sum({<Month={'>=$(=max(Month)-11)<=$(=max(Month))'}>}Amount)