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: 
Not applicable

Customers that have not bought anything within

I have a salestable with saledates and customerid.

DateCustomeridSalesAmount
2012-06-120015
2012-06-070025
2012-04-2900110

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.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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?

View solution in original post

4 Replies
swuehl
MVP
MVP

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

Not applicable
Author

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

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?

View solution in original post

Not applicable
Author

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!