topic 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. in QlikView

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.

neena123 — Wed, 01 Jun 2016 15:51:26 GMT

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])

Re: 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.

sunny_talwar — Wed, 01 Jun 2016 15:52:59 GMT

May be this:

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

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

Re: 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.

neena123 — Wed, 01 Jun 2016 15:57:17 GMT

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

Re: 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.

Wed, 01 Jun 2016 20:03:36 GMT

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

Re: 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.

neena123 — Wed, 01 Jun 2016 20:32:47 GMT

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

Re: 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.

Thu, 02 Jun 2016 07:10:30 GMT

the following expression can be used:

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