Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
QlikWilliam
Contributor II
Contributor II

Issue with Overlapping dimensions

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)"}

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.
Labels (2)
1 Solution

Accepted Solutions
Chanty4u
MVP
MVP

Try this 

Sum({<MonthsAgo={0,1,2}>} Sales) + Sum({<MonthsAgo={1,2,3}>} Sales)

Or 

Sum(Aggr(

    Sum({<R03_MonthsAgo>} Sales), 

    R03_MonthsAgo, MonthsAgo))

View solution in original post

3 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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,

Chanty4u
MVP
MVP

Try this 

Sum({<MonthsAgo={0,1,2}>} Sales) + Sum({<MonthsAgo={1,2,3}>} Sales)

Or 

Sum(Aggr(

    Sum({<R03_MonthsAgo>} Sales), 

    R03_MonthsAgo, MonthsAgo))

QlikWilliam
Contributor II
Contributor II
Author

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))