topic Re: Date range in set analysis in QlikView

Date range in set analysis

Wed, 26 Feb 2014 09:14:51 GMT

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!

Re: Date range in set analysis

Nicole-Smith — Thu, 27 Feb 2014 19:14:10 GMT

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)

Re: Date range in set analysis

Clever_Anjos — Thu, 27 Feb 2014 20:06:21 GMT

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)

Re: Date range in set analysis

whiteline — Thu, 27 Feb 2014 20:36:54 GMT

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.

Re: Date range in set analysis

Fri, 28 Feb 2014 08:51:22 GMT

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!