topic Re: Rolling window comparision in QlikView

Rolling window comparision

Fri, 25 Mar 2016 12:01:46 GMT

Hi All,

I wanted to create a table which will compare the metrics value with current year/quarter, previous year/quarter and previous to previous quarter based on user selection.

Please refer below screenshot which is created in Power-Pivot -

User selection => Calendar Year = 2014 and Month-Name = 'March'.

First table shows data Quarter-wise where-in [March is the end interval]

I should see data from January to March for current year(2014), same period last year and last to last year.

Second table shows data Year-wise where-in [March is the end interval]

I should see data from April to March for current year(2013-14), same period last year and last to last year.

I am able to write Set Analysis Expression for calculating data Year-wise using the following Set Analysis -

Sum({<[Calendar Year] = {$(=ONLY([Calendar Year]) - 1)}>}[Metrics1]) -- Previous Year

Sum({<[Calendar Year] = {$(=ONLY([Calendar Year]) - 1)}>}[Metrics1]) -- Previous to Previous Year

But I am not sure how to use Month Name in order to go back for last 3 months. I tried below expression

Sum({<[MonthName]={'>=$(=MAX([MonthName])-2)<=$(=MAX([MonthName]))'}>}[Metrics1]).

Also, how to handle the Jan and Feb month where-in we need to go back an year. Like, when I select Feb 2012, then I should see data from Dec 2011 to Feb 2012.

Thank you for your help.

Re: Rolling window comparision

Gysbert_Wassenaar — Fri, 25 Mar 2016 12:18:49 GMT

You can't use a month name for this. April comes after March, but not sorted alphabetically. And it doesn't get you across year borders. You need to use a serial month number for this or a date field.

LOAD

MyDate,

Year(MyDate)*12+Month(MyDate) as MonthSerial,

....other fields....

FROM

...

Then you can do things like

sum({<Year=,Month=,MonthSerial={'>=$(Max(MonthSerial)-2)<=$(Max(MonthSerial))'}>}Metric1)

Re: Rolling window comparision

Fri, 25 Mar 2016 13:50:07 GMT

Hi Gysbert,

Even after using MonthSerial column in Set Analysis expression, I can't see the aggregated value of last 3 months when I select month name from MonthName column.

sum({<Year=,Month=,MonthSerial={'>=$(Max(MonthSerial)-2)<=$(Max(MonthSerial))'}>}[Metric1])

Re: Rolling window comparision

Gysbert_Wassenaar — Fri, 25 Mar 2016 15:24:08 GMT

Post a small qlikview document that demonstrates the problem.

Re: Rolling window comparision

Mon, 28 Mar 2016 11:26:58 GMT

Hi Gysbert,

It worked.

Can you please tell me how to generate those Table Headers [April 2013 - March 2014] dynamically?

And which chart component would you recommend to create above table? Currently I am using KPI's only