I have an issue and im not sure there is a solution except to rework the data model.
Im trying to create a variable called "R03_YM0_1" that will calculate the rolling 3 month value for the current month (MonthsAgo=0) and previous month (MonthsAgo=1) and sum this in a KPI.
The variable is defined in the script like this
R03_YM0_1|R03 1 to 2 months back from current YearMonth|R03_MonthsAgo={"<=@(=min(MonthsAgo)+1)>=@(=min(MonthsAgo)+0)"}
In Qlik Sense the variable evaluates to this:
Sum( {<R03_MonthsAgo={"<=1>=0"}>} Sales)
Where each field value in the field "R03_MonthsAgo" is associated to 3 field values of another field called "MonthsAgo". For Instance R03_MonthsAgo = 0 is associated with MonthsAgo=0,1,2 and R03_MonthsAgo = 1 is associated with MonthsAgo=1,2,3
I then have Sales for each MonthsAgo value.
With the expression
Sum( {<R03_MonthsAgo={"<=1>=0"}>} Sales)
it does not evaluate to the same as the expression Sum( {<R03_MonthsAgo={'0'}>} Sales) + Sum( {<R03_MonthsAgo={'1'}>} Sales) in terms of sales.
This is because the expression
Sum( {<R03_MonthsAgo={"<=1>=0"}>} Sales)
does not double count overlapping values. That is it calculates sales for MonthsAgo=0,1,2,3 but that is not what I want. I want it to double count the MonthsAgo values 1 and 2 since these are overlapping.
Is there another solution to this? I can write Sum( {<R03_MonthsAgo={'0'}>} Sales) + Sum( {<R03_MonthsAgo={'1'}>} Sales) and I will get the correct value in the KPI, but this is troublesome to write out each month like this.
I think the only way to make Qlik double count these numbers is to use a sum of two aggregations. Any other Set Analysis solution would naturally remove the duplication.
