Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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?
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
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
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?
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!