Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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 ?
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?
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.
Still confusing. Would you be able to provide the expected output should look like on front end?
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?
Here is what it look like with my formula:
(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.
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.
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 ?