Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
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
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.
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