Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi!
I'm trying to make a date range selection using set analysis.
I found this great discussion:
http://community.qlik.com/thread/82437
Unfotunately i can't get it to work.
Here is my expression:
Count({$<Date_Year_Month={">=$ (=Date(AddMonths(Max(Date_Customer), -11), 'YYYY-MM')) <$ (=Date(Max(Date_Customer), 'YYYY-MM'))"}>} DISTINCT IM)
The field Date_Year_Month has the values as YYYY-MM
The field Date_Customer looks like YYY-MM-DD
I've also tried to put the calculations for the dates in variables and double checked so that they put out the correct dates by putting the variables in a text box.
With the dates in variables the expression looks like below:
Count({$<Date_Year_Month={">=$ (='vMax_Months_minus1year') <$ (='vMax_Months')"}>} DISTINCT IM)
Any help would be much appreciated!
Is it just not working when you use the variables? Your syntax is a bit off:
Count({$<Date_Year_Month={'>=$(vMax_Months_minus1year)<$(vMax_Months)'}>} DISTINCT IM)
I usually use a set operator "*"
Count({$<Date_Year_Month={">=$ (=Date(AddMonths(Max(Date_Customer), -11), 'YYYY-MM'))"}*{"<$ (=Date(Max(Date_Customer), 'YYYY-MM'))"}>} DISTINCT IM)
Hi.
To simplify the madness of date format conversion in set analysis you can use search capabilities using search expression.
Count({$<Date_Year_Month={"=Date_Year_Month>=AddMonths(Max(Date_Customer), -11) and Date_Year_Month<Max(Date_Customer))"}>} DISTINCT IM)
You can also optimize/simplify it by using $-sign expansion and variables.
Thank you all for your help!
For some strange reason i couldn't get any of the proposed solutions to work.
What I ended up doing was to create a variable called vLastYear that looks like this:
=chr(39)&Date(AddMonths(Max(Date_Customer), -11), 'YYYY-MM') & chr(39) & ', '&
chr(39) & Date(AddMonths(Max(Date_Customer), -10), 'YYYY-MM')& chr(39) & ', '&
chr(39) & Date(AddMonths(Max(Date_Customer), -9), 'YYYY-MM')& chr(39) & ', '&
chr(39) & Date(AddMonths(Max(Date_Customer), -8), 'YYYY-MM')& chr(39) & ', '&
chr(39) & Date(AddMonths(Max(Date_Customer), -7), 'YYYY-MM')& chr(39) & ', '&
chr(39) & Date(AddMonths(Max(Date_Customer), -6), 'YYYY-MM')& chr(39) & ', '&
chr(39) & Date(AddMonths(Max(Date_Customer), -5), 'YYYY-MM')& chr(39) & ', '&
chr(39) & Date(AddMonths(Max(Date_Customer), -4), 'YYYY-MM')& chr(39) & ', '&
chr(39) & Date(AddMonths(Max(Date_Customer), -3), 'YYYY-MM')& chr(39) & ', '&
chr(39) & Date(AddMonths(Max(Date_Customer), -2), 'YYYY-MM')& chr(39) & ', '&
chr(39) & Date(AddMonths(Max(Date_Customer), -1), 'YYYY-MM')& chr(39) & ', '&
chr(39) & Date(Max(Date_Customer), 'YYYY-MM')& chr(39)
This result from the variable:
'2013-02', '2013-03', '2013-04', '2013-05', '2013-06', '2013-07', '2013-08', '2013-09', '2013-10', '2013-11', '2013-12', '2014-01'
Then I did the following set analysis in the graph:
Count({$<Date_Year_Month={$(=vLastYear)}>} DISTINCT IM)
Its not the prettiest solution but it gets the job done.
Again, thank you all for your help!