Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi!
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)"}
Try this
Sum({<MonthsAgo={0,1,2}>} Sales) + Sum({<MonthsAgo={1,2,3}>} Sales)
Or
Sum(Aggr(
Sum({<R03_MonthsAgo>} Sales),
R03_MonthsAgo, MonthsAgo))
Hi William,
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.
Cheers,
Try this
Sum({<MonthsAgo={0,1,2}>} Sales) + Sum({<MonthsAgo={1,2,3}>} Sales)
Or
Sum(Aggr(
Sum({<R03_MonthsAgo>} Sales),
R03_MonthsAgo, MonthsAgo))
Thanks for the replies. This also worked and might be the best solution to the problem
Sum(Aggr(
Sum({<R03_MonthsAgo>} Sales),
R03_MonthsAgo, MonthsAgo))
I can add the variables like this, for example last 12 months of R06 data
Sum(Aggr(
Sum({<$(vD.R06_YM0_11)>} Sales),
R06_MonthsAgo, MonthsAgo))