And I want measures using set analysis that calculates This Year Sales and Last Week Sales (where the set analysis will pick up the previous week).
However, this will then create 2 lines.
For example if it is week 46 2025, then it will have 2 lines showing Week = 45 and 46, with TY Sales populated on line with 46, and LW Sales populated on line with 45 on it.
Week
TY Sales
LW Sales
45
0
100
46
200
0
I have tried set analysis = Sum({<Year={2024},Week={45}>}[Sales Value Gross])
And also an aggregated expression, but nothing will work.
Apologies, I probably didn't explain this fully. I want it to be able to reference back to the calendar and see what the comparison year week field is so that it can look for possible values - this is what I previously tried:
Sum({<IT_Year_Week = P({LY} Compare_YearWeek_LY)>} [Sales Value Gross])
Your above suggestion does work when hard coded but I can't have a hard coded set analysis.
Thanks for the suggestions, we want the option to use a comparison week year field from the calendar table so that if we were comparing last week or last year values we could actually switch out the weeks - meaning it might not always be week - 1, or same week of last year for example.
What you are trying to do isn't possible because the set analysis respectively the defined selection state couldn't overwrite the relationship to the underlying data. The data from week 45 doesn't belong to the week 46 and reverse.
The simplest approach in many scenarios is to remove the period-fields from the chart and to replace the dimension-values with n expressions. If really a dimensional layer is needed it requires appropriate adjustments within the data-model - by unifying the period-information and/or extending them, for example with The As-Of Table - Qlik Community - 1466130.
