topic Re: Year Before comparison doesn't work fully for a selected period in App Development

Year Before comparison doesn't work fully for a selected period

Stanislav1 — Wed, 13 Nov 2024 08:20:13 GMT

Hello, Qlik community!

I have a line chart with 3 measures, the last measure is comparison with Year Before. Currently the Year Before line shows only the year before direct purchases for 2022 and as you cans see the red line in 2023 is null, which should show 2022 data. The years can be chosen from a date filter.

The calculation behind this is complex and the mistake might be in either the dimension or the measure expression. This is the dimensions expression: =If($(vL.UI.time.Field)>=vL.time.From,Date([#$(vL.UI.time.Field)], 'DD MMM'),Date([#$(vL.UI.time.Field)]+$(vL.time.ToShift),'DD MMM'))

Measure expression:

=if(vL.selected.Measure.Chart='$ After-trial purchases',Num([msr11 (Compare)],'$ # ##0'),

if(vL.selected.Measure.Chart='$ Direct purchases', Num([msr08 (Compare)],'$ # ##0'),

if(vL.selected.Measure.Chart='$ One-off purchases', Num([msr15 (Compare)],'$ # ##0'),

if(vL.selected.Measure.Chart='$ Renewals', Num([msr33 (Compare)],'$ # ##0'),

if(vL.selected.Measure.Chart='$ App purchases', Num([msr59(Compare)],'$ # ##0'),

if(vL.selected.Measure.Chart='$ Report corrections', Num([$ RC (Compare)],'$ # ##0'),

if(vL.selected.Measure.Chart='$ Total FR',Num([msr107 (Compare)],'$ # ##0'),Num([msr11 (Compare)],'$ # ##0'))))))))

I suspect that this issue will be in the dimension expression, because the selected period covers the same dates in two different years. How can I correct the code to distribute the data in both years, in this case 2022 and 2023 and the year before data (for 2023 show 2022 and for 2022 show 2021)

I am open to any suggestions and if you need any additional information I can provide it.

Re: Year Before comparison doesn't work fully for a selected period

brunobertels — Wed, 13 Nov 2024 11:41:10 GMT

May have a look to this post

https://www.qreatic.fr/dailyqlikchallenge/2021/6/04/69-same-day-previous-year-comparison-in-qlik-sense

The trick is to create a pivot calendar in the script between your calendar table and fact table :

Load script to generate it from Facts :

Pivot:
Load distinct Date_ID as Date_ID, 
	 Date_ID as Date_Cal,
	 'P' as TypeDate
resident Sales;
Load distinct Date_ID-364 as Date_ID, 
	 Date_ID as Date_Cal,
	 'SameDay' as TypeDate
resident Sales;

then adapt your mesure for previous year with set analysis :

Current Year : Sum({$<TypeDate={'P'}>}Sales)

Same Day previous year : Sum({$<TypeDate={'SameDay'}>}Sales)

Hope it helps

Re: Year Before comparison doesn't work fully for a selected period

Stanislav1 — Wed, 13 Nov 2024 13:59:59 GMT

Thank you for your suggestion, but it doesn't help for this issue