Customers that have not bought anything within

[#1]

I have a salestable with saledates and customerid.

Date	Customerid	SalesAmount
2012-06-12	001	5
2012-06-07	002	5
2012-04-29	001	10

By selecting dates I want to show in my graph every customer that have purchased anything in the selected period BUT have not purchased anything within 3 months prior to the chosen period. How can I do this?

In the example table, If June 2012 is selected, only customer 002 should be counted.

[swuehl]

I've loaded your data like this, creating a Month field:

Set DateFormat = 'YYYY-MM-DD';

LOAD MonthName(Date) as Month,

           Date,

     Customerid,

     SalesAmount

FROM

[http://community.qlik.com/thread/67003?tstart=0]

(html, codepage is 1252, embedded labels, table is @1);

Then, I've created a straight table chart with dimension Customerid and as expression:

sum(

{<Customerid = p()*e({<Month= {">=$(=addmonths(max(Month),-3))<$(=max(Month))"}, Date>}), Date >} SalesAmount)

If you are using several calendar fields, you might need to clear these fields in the set expression as shown (maybe also within the p() function, depending on your requirement).

Hope this helps,

Stefan

[#3]

Wow, thanks for a great response. However, I still have some problems with this. I have done like this, the application is primarily using YearMonth when selecting sales.

Sum({$<CustomerId=p()*e({<Date={">=$(=AddMonths(Max(Date),-3))<$(=Max(Date))"}, YearMonth=>}), YearMonth=>}Sales)

But it doesnt work. It only works when I select one specific CustomerId

[swuehl · Accepted solution]

Try using YearMonth in the set modifier instead Date and clearing Date field.

If you still having troubles to get it right, would it be possible that you post a small sample .qvw file?

[#5]

Thanks Stefan!

It works. I tried using YearMonth before but didnt work. But I just saw that the Addmonth expression turned my Yearmonth into a date with day also. When changing it, it worked. Thanks for your help!