topic Re: Rolling 12 month set analysis in QlikView

Rolling 12 month set analysis

Tue, 09 Jun 2015 12:48:18 GMT

Hi,

I thought I had an answer for my rolling 12 month problem, but unfortunately it doesn't work correctly.

This is my expression inkl. set analysis:

Count({<closed_yearmonth={"<=$(=date(MonthStart(Today(),-1),'YYYYMM'))) >= $(=Date(MonthStart(Today(),-13), 'YYYYMM'))"}>}DISTINCT ID)

I assumed that my bar char would show the values from May 2014 to May 2015 (ok, we only have data starting from April 2015, so April 2015 and May 2015) - but it shows June data as well.

Does anyone know why I have June data in there?

Thanks in advance!

Re: Rolling 12 month set analysis

Anonymous — Tue, 09 Jun 2015 14:51:09 GMT

Hi,

I hope this help you!

Count({$< closed_yearmonth= {"<=$(=addmonths(max(Datefiel),-12,1))>=$(=addmonths(min(DateFiel),-12))"}>}Distinct ID)

Regards!!.

Re: Rolling 12 month set analysis

Wed, 10 Jun 2015 06:48:33 GMT

Hi,

thank you.

I adjusted it to

Count({$< closed_yearmonth= {"<=$ (=date(AddMonths (max(MonthStart(today())),-13),'YYYYMM')) >= $(=date(AddMonths(min(MonthStart(today())),-13),'YYYYMM')) "}>}Distinct ID)

but I have the same issue here. It doesn't matter if I choose -1 or -12 or -20, it always displays all data I have

Re: Rolling 12 month set analysis

PrashantSangle — Wed, 10 Jun 2015 06:57:27 GMT

Hi,

Try like

Count({$< closed_yearmonth= {"<=$ (=date(MonthStart(today())-1,'YYYYMM')) >= $(=date(AddMonths(MonthStart(today()),-12),'YYYYMM')) "}>}Distinct ID)

Regards

Re: Rolling 12 month set analysis

Wed, 10 Jun 2015 06:59:22 GMT

I think I solved by myself. I think there was one bracket too much

Count({$<closed_yearmonth={"<=$(=date(MonthStart(Today(),-1),'YYYYMM')) >= $(=Date(MonthStart(Today(),-13), 'YYYYMM'))"}>}DISTINCT ID)

Re: Rolling 12 month set analysis

stevedark — Wed, 10 Jun 2015 07:15:40 GMT

There is another way of approaching this without Set Analysis, which I find quite flexible and useful.

I've described it in this blog post:

http://www.quickintelligence.co.uk/qlikview-accumulate-values/

Steve