Rolling 10 weeks in a QlikView line chart Leap Year issue

[AndersGS]

Hi guys,

In a line chart I'd like to show a rolling 10 week sale development for current and last year. I'm using "week" as Dimension and two expressions to present the sales data, please see example below. Problem with the leap year is that 2021 week 1 compares to 2020 week 2 etc. so the "week" Dimension won't work (at least this year). Any ideas on how to solve this?  

Example Data:

Week	Sales Current Year	Sales Last Year
1	150	155
2	100	95
3	80	100
4	110	115
5	160	150
6	130	145
7	120	100
8	100	95
9	95	100
10	105	115

Running QlikView 11.2 SR 18

Thanks,
Anders

[chrismarlow]

Hi,

Is this an issue with leap year, or rather an issue with how the weeks break over end of the year?

There are optional parameters broken_weeks  & reference_day in the week function that might help you get the week numbering working in a way that is consistent for your analysis;

week - script and chart function ‒ QlikView

Cheers,

Chris.

[AndersGS]

Thanks Chris. Broken Weeks could be an option but we are running QV 11.2 which I think only supports one parameter in Week.  Are there any other possible ways around this? 

Anders

[chrismarlow]

Hi,

Do you have to do this with expressions or could you do something in script? At an extreme could you feed a spreadsheet in with the 'correct' week mapping to join to your calendar?

There may be something possible similar to the fudge that worked on your other query, can you attach some dummy sample data/how you would expect your dates to work (so a couple of week 1/2 etc for a couple of years).

Cheers,

Chris.

[AndersGS · Accepted solution]

Hi Chris,

I'm working on adjusting the expressions now, including a variable that checks whether last year has 53 weeks (TRUE/FALSE) - as this is the issue here and not the leap year as you said. Thanks for feedback and help so far.

Anders