Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
How to achieve below requirement Last 12 months, Last 3months, QTD
Month-Year | Value | sum of value for last 12 months for each month(Last 12 Months) | sum of value for last 3 months for each month(Last 3 Months) | QTD |
Jan-2018 | 10 | 10 | ||
Feb-2018 | 30 | 40 | ||
Mar-2018 | 50 | 90 | 90 | |
Apr-2018 | 100 | 180 | 100 | |
May-2018 | 200 | 350 | 300 | |
Jun-2018 | 40 | 340 | 340 | |
Jul-2018 | 50 | 290 | 50 | |
Aug-2018 | 60 | 150 | 110 | |
Sep-2018 | 75 | 185 | 175 | |
Oct-2018 | 96 | 231 | 96 | |
Nov-2018 | 77 | 248 | 173 | |
Dec-2018 | 45 | 833 | 218 | 218 |
Jan-2019 | 98 | 921 | 220 | 98 |
Feb-2019 | 78 | 969 | 221 | 176 |
Mar-2019 | 65 | 984 | 241 | 241 |
Apr-2019 | 90 | 974 | 233 | 90 |
May-2019 | 80 | 854 | 235 | 170 |
Jun-2019 | 77 | 891 | 247 | 247 |
Jul-2019 | 67 | 908 | 224 | 67 |
Aug-2019 | 56 | 904 | 200 | 123 |
Sep-2019 | 98 | 927 | 221 | 221 |
Oct-2019 | 23 | 854 | 177 | 23 |
Nov-2019 | 43 | 820 | 164 | 66 |
Dec-2019 | 55 | 830 | 121 | 121 |
there are a few brilliant solutions to this problem using complex expressions that gives you insight into how you can build expressions in QLik. i prefer to move the complexity to the script and build bridges for this. when a user selects a date, you allow for association to the fact for any time periods you want. this results in simple expressions that are faster and easier to maintain + troubleshoot.
Fact:
load Date#(Date, '$(DateFormat)') as FactDate, Measure inline [
Date, Measure
Jan-2018,10
Feb-2018,30
Mar-2018,50
Apr-2018,100
May-2018,200
Jun-2018,40
Jul-2018,50
Aug-2018,60
Sep-2018,75
Oct-2018,96
Nov-2018,77
Dec-2018,45
Jan-2019,98
Feb-2019,78
Mar-2019,65
Apr-2019,90
May-2019,80
Jun-2019,77
Jul-2019,67
Aug-2019,56
Sep-2019,98
Oct-2019,23
Nov-2019,43
Dec-2019,55
];
NoConcatenate
Calendar:
load distinct FactDate as Date, QuarterEnd(FactDate) as Quarter
resident Fact;
NoConcatenate
tmpBridge:
load Date, Quarter Resident Calendar;
inner join (tmpBridge) load Date as FactDate, Quarter as FactQuarter Resident tmpBridge;
NoConcatenate
Bridge:
load Date, FactDate, '12MRoll' as DateType Resident tmpBridge
where FactDate >= addmonths(Date,-11) and FactDate<=Date;
Concatenate (Bridge)
load Date, FactDate, '3MRoll' as DateType Resident tmpBridge
where FactDate >= addmonths(Date,-2) and FactDate<=Date;
Concatenate (Bridge)
load Date, FactDate, 'QTD' as DateType Resident tmpBridge
where FactQuarter = Quarter and FactDate<=Date;
drop table tmpBridge;
this is how you use it:
hope that helps
i didnt apply the rule for the 12month rolling to ignore the first so many months and added an if statement in the expression in case you made a mistake in your sample as those months still have rolling 12 month periods albeit not complete. you can also build that into your script to make the expressions even simpler
Thanks for the information, I will try to figure it out for more. Keep sharing such informative post keep suggesting such post.