Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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.
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.
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?
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.
Thanks @NageshSG ,
It works now😊