Make YTD comparison with only a year-week field

[Qliksense_77]

Hi,

How do I make a KPI that compare YTD sales for current and last year, when I only have a date field like below?

Period_type	Period	Sales
Week	2021W04	121
Week	2021W03	100
Week	2021W02	120
Week	2021W01	121
Week	2020W53	121
Week	2020W52	111
Week	2020W51	154
Week	2020W50	132
Week	2020W49	121
Week	2020W48	98
Month	2021M01	262
Month	2020M12	188
Month	2020M11	256

[NageshSG]

Hi @Qliksense_77 

As I understand from the table, the Period_type defines the Period which are in buckets of weeks or months.
Since the 'date' field is rounded off to a week or a month, you can extract out the Year from Period and create a 'Year' field in the script as below:
Please note that the actual underlying calendar transaction date may spill over the 'next/previous year' like for example Week 53/52 or Week 01.

Script side:
Load
Period_type,
Period,
left(Period,4) as Year,
num(right(Period,2)) as Wk_Mth_Number,
Sales
from <data_source>;

Front end / Layout:

You can derive the current YTD / last year YTD (based on the completed weeks/months) as under:
Current Year to Week Sales = Sum({<Period_type={'Week'},Wk_Mth_Number={"<=$(=Max(Wk_Mth_Number))"},Year={$(=Max(Year))}>}Sales)
Previous Year to Week Sales= Sum({<Period_type={'Week'},Wk_Mth_Number={"<=$(=Max(Wk_Mth_Number))"},Year={$(=Max(Year)-1)}>}Sales)
Current Year to Month Sales = Sum({<Period_type={'Month'},Wk_Mth_Number={"<=$(=Max(Wk_Mth_Number))"},Year={$(=Max(Year))}>}Sales)
Previous Year to Month Sales= Sum({<Period_type={'Month'},Wk_Mth_Number={"<=$(=Max(Wk_Mth_Number))"},Year={$(=Max(Year)-1)}>}Sales)

Hope this helps.

Regards.

[Qliksense_77]

Thanks @NageshSG ,

It works for current year. But the expression for last year does not work. The expressions for last year include all weeks/months. You know what the problem can be?

[NageshSG · Accepted solution]

Hi @Qliksense_77 ,

Good question! I had added some dummy data for 2020 Wk 1to 4.

See how this works in the attached sample qvw file. Weeks and Months will overlap, hence need to fix the measure in the set expression. Either the Week YTD is enabled or the Month YTD for easy reference.

Also the screen shot below:

Regards.

[Qliksense_77]

Thanks @NageshSG , 

It works now😊