topic Re: Sequential Rolling Weekly Growth over Last Year in QlikView

Sequential Rolling Weekly Growth over Last Year

Anonymous — Thu, 07 May 2015 20:10:39 GMT

I am trying to create a year over year Growth calculation, but I keep having Nulls returned.

We have 13 periods in a year, so we can't use a standard calender to get year over year information. In order to do Rolling charts, we have sequential weeks, periods, days, etc as part of the calender table.

I can't get the Aggr function to return the Previous years weekly data on the same Records in the chart using the sequential columns. I can use the Aggr function to return prior year data within the same record when I am not using our the sequential column.

I've also tried using the Concat, but I just not sure if I am using it right. Can anyone point me to the correct Function or syntax to get this to work. I have attached the QVW with a chart showing my null results when I am just trying to get the difference b/w the two years data.

Re: Sequential Rolling Weekly Growth over Last Year

Gysbert_Wassenaar — Fri, 08 May 2015 09:24:23 GMT

Perhaps this document helps: Calculating rolling n-period totals, averages or other aggregations

Re: Sequential Rolling Weekly Growth over Last Year

Anonymous — Fri, 08 May 2015 15:31:51 GMT

Thank you for the response. That document shows me how to calculate sequential rows within a table, I did review it prior creating this thread. It is very informative, but I it isn't exactly what I am trying to do.

What I am trying to accomplish is to calculate growth from one year to the next for specific matching weeks of the fiscal year. Since we use a non traditional calender, the only way to show the last 12 weeks when there is a change in year is to have Sequential numbering of Weeks, Periods, Days, etc. Meaning when live the chart will be able to show the last 12 weeks even when the new year begins.

So in essence I need to show in my chart 12 weeks of data. But that data doesn't accumulate rows of data it is only showing me the results of the calculations. I want to be able to compare Period 4 week 2 of 2014 to Period 4 week 2 of 2015 and so on. If this happened to be the beginning of the fiscal year, I would be able to see Period 12 week 1 of 2014 through Period 1 week 4 of the 2015 which I could compare to Period 12 week 1 of 2013 through Period 1 week 4 of 2014.

Re: Sequential Rolling Weekly Growth over Last Year

Gysbert_Wassenaar — Fri, 08 May 2015 16:36:23 GMT

What I think you should do is create an AsOf table with records that relate a week with the same week a year ago. The script below is just an example. I don't know how you handle the 365th and 4-yearly 366th day. Perhaps it's easier to generate an AsOf table in Excel and load it from that.

Qualify *;

UnQualify wk_cnt;

AsOf:

LOAD

fiscal_yr,

prd_of_year,

wk_of_prd,

wk_of_yr,

wk_cnt as AsOf.wk_cnt

wk_cnt - 52 as wk_cnt,

'YearAgo' as ReportPeriod

RESIDENT Master;

Concatenate(AsOf)

LOAD

fiscal_yr,

prd_of_year,

wk_of_prd,

wk_of_yr,

wk_cnt as AsOf.wk_cnt

wk_cnt,

'Current' as ReportPeriod

RESIDENT Master;

UnQualify * ;

You can then use a period field from the AsOf table as dimension and expressions like:

Current week: sum({<ReportPeriod ={'Current'}>}Value)

Same week a year ago: sum({<ReportPeriod ={'YearAgo'}>}Value)

Re: Sequential Rolling Weekly Growth over Last Year

Anonymous — Fri, 08 May 2015 21:47:13 GMT

Thanks I will try to manufacture the AsOf table solution on Monday.

As for how we handle the Extra day, every so many years Pizza Hut solves this by adding a 5th week to the 13th period. Which of course will throw a monkey wrench into the works.