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;
