Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
alci
Contributor II
Contributor II

Sum on dimensions, but average on time

Hi,

I am trying to solve a probably common problem, and fail at it until now...

I have measures that need to be summed on most dimensions, but averaged on time. These are typically Full-time-equivalent, or warehouse quantities.

Here is a simple example, with items in warehouse:

stk:

LOAD * INLINE [

item, warehouse, year, month, quantity

i1, w1, 2020, 01, 20

i1, w2, 2020, 01, 10

i1, w1, 2020, 02, 15

];

I would like to calculate these values for the selected dimensions:

i1 / 2020 / 01 => 30 (20 in w1, 10 in w2)

i1 / w1 / 2020 => 17.5 (20 in january, 15 in february, 17.5 on average)

i1 / 2020 => 22.5 (30 in january, 15 in february, 22.5 on average)

I have tried this formula:

Avg(Aggr(Sum(If(isNull(quantity), 0, quantity)), year, month)

But it does not work the way I wish. Here is what I get:

i1 / 2020 / 01 => 35 (I don't understand)

i1 / 2020 => 27.5 (because there are two entries in january I suppose)

i1 / w1 / 2020 => 35 (no average is done)

 

So... I clearly don't understand how it works... Any help would be appreciated.

Labels (2)
1 Solution

Accepted Solutions
alci
Contributor II
Contributor II
Author

I finally think it is as simple as adding a yearmonth column and doing:

sum(if(isNull(qty),0,qty)) / count(distinct yearmonth)

 

No need for Aggr or Avg. Maybe count distinct comes with a performance cost ?

View solution in original post

7 Replies
Kushal_Chawda

Can you elaborate? Do you want different calculation to be done based on different selection  with single expression or want to create 3 different expression?

alci
Contributor II
Contributor II
Author

I'd rather have a single expression that would be available in "Main items" (not sure about the english name). But several expressions would be ok if there is no better solution.

Kushal_Chawda

Still confusing. Would you be able to provide the expected output should look like on front end?

sunny_talwar

Is this something you are using in the pivot table? where you expand and collapse dimensions? Are is this like a master measure you are trying to create which can be used across different objects which might have different dimensions in them?

alci
Contributor II
Contributor II
Author

Here is what it look like with my formula:

 

qlik.png

 

(1) shows most detailed data, as loaded

(2) should show the average stock by item for year 2020. For i1, this is wrong, as the value should be 25 (20+10 in january, 20 in february, so the average is 25). For i2 it is null, but it should 2.5 (0 in january, 5 in february)

Last table shows quantity by item and warehouse in 2020. Fist line (i1 / w1) should be 5 ( (10+0) / 2 months), second line (i1 / w2) is fine ( (20 +20) /2 months). And third line (i2 / w1) should be 2.5 ( (0+5) /2 months)

 

And yes, idealy, it would be a master measure to be used accross different objects, if this is at all possible.

sunny_talwar

It would be somewhat challenging to have a single measure which determine different expressions based on different dimensions. If these three dimensions are the only possibility of dimensions, you might still be able to code it using an if statement. But, if you are talking about more permutation and combination of dimensions, then it might just be easier to use an individual expression for each chart or at least an Avg master measure and a sum master measure.

alci
Contributor II
Contributor II
Author

I finally think it is as simple as adding a yearmonth column and doing:

sum(if(isNull(qty),0,qty)) / count(distinct yearmonth)

 

No need for Aggr or Avg. Maybe count distinct comes with a performance cost ?