topic Re: Issue with Overlapping dimensions in App Development

Issue with Overlapping dimensions

QlikWilliam — Fri, 28 Feb 2025 13:47:46 GMT

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.

Re: Issue with Overlapping dimensions

Oleg_Troyansky — Fri, 28 Feb 2025 14:55:07 GMT

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,

Re: Issue with Overlapping dimensions

Chanty4u — Fri, 28 Feb 2025 16:13:31 GMT

Try this

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

Sum(Aggr(

Sum({<R03_MonthsAgo>} Sales),

R03_MonthsAgo, MonthsAgo))

Re: Issue with Overlapping dimensions

QlikWilliam — Tue, 04 Mar 2025 11:26:00 GMT

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