4 Replies Latest reply: Mar 28, 2016 7:26 AM by Rameshwar Pawale

# Rolling window comparision

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.

• ###### Re: Rolling window comparision

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.

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

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

Post a small qlikview document that demonstrates the problem.

• ###### Re: Rolling window comparision

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