Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Rolling sum of sales over previous weeks

Hi All,

For some days now the problem below is my top priority. However I seem to be unable to solve it. Maybe someone here can help:

Please consider a database table with salesrecords.

Fields: Year, Week, Salesvalue

There is around 6 years of sales available.

I'm looking for a solution in which I can make a rolling 12 week total. For example:

2010 - 47 - 600

2010 - 48 - 500

2010 - 49 - 550

2010 - 50 - 400

2010 - 51 - 350

2010 - 52 - 400

2011 - 1 - 100

2011 - 2 - 150

2011 - 3 - 125

2011 - 4 - 130

2011 - 5 - 75

2011 - 6 - 700

2011 - 7 - 775

Feelds needed: This week; Last week; Last 4 weeks; Last 12 weeks

Selecting Year 2011, Week 7 Should result in:

YearWeekThis WeekLast WeekLast 4 weeksLast 12 weeks
20117775700775+700+75+130775+700+75+...+550+500

Selecting Year 2011, Week 6 Should result in:

YearWeekThis WeekLast WeekLast 4 weeksLast 12 weeks
2011670075700+75+130+125700+75+130+...+500+600

Fields this week and last week I resolved myself. For this I used set analysis. The 2 last fields `last 4 weeks` and `last 12 weeks` are causing me a headache especially when taking into account the change of years.

I tried working with a week counter; but this solution is not acceptable to management as they just want to select year and week.

Can anyone help?

Thanks and regards,

Wim

2 Replies
MVP
MVP

Re: Rolling sum of sales over previous weeks

Hi Wim,

Please go through the attached document, hope this helps you.

Regards,

Jagan.

Not applicable

Rolling sum of sales over previous weeks

Thanks Jagan,

I tried your solution and in the provided scenario it works.

However when implemented with real data I do of course have dimensions such as Customer, Product, etc. In this implementation the total running 12 weeks for each customer is the same as the total for all customers. Adding to the variable `customer.name=` only helps when an actual customer is selected and thus no cross customer comparison is possible. Is there a work around for this so that it works flexible with dimensions?

Thanks and regards,

Wim

Community Browser