Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
mruehl
Partner - Specialist
Partner - Specialist

Average and calculated dimensions

Hey,

I want to show the Average Stock Quantity of the Top 5 suppliers.
Top 5 means the 5 suppliers with the highest Average Stock Quantity.

mruehl_0-1705575642321.png

The calculated dimension works as expected. I get the 5 suppliers with the highest average Stock Quantity.

mruehl_1-1705576097482.png

This expression works fine for the non-calculated dimension on the right side:

mruehl_2-1705576267166.png

 

But on the left side, the value of "Sonstige" (others) is not correct.

Where is my mistake? 🤔

 

 

Labels (1)
1 Solution

Accepted Solutions
Rohan
Specialist
Specialist

Hi @mruehl ,

Try this once :

=if(dimensionality()=0,
avg(aggr(sum({<%DATEKEY_BESTAND={">=$(=addmonths(vToday,-12))"}>}Bestandsmenge)),%DATEKEY_BESTAND)),
avg(aggr(sum({<%DATEKEY_BESTAND={">=$(=addmonths(vToday,-12))"}>}Bestandsmenge)),%DATEKEY_BESTAND,HauptLieferant))/
Count({<%DATEKEY_BESTAND={">=$(=addmonths(vToday,-12))"}>} Distinct HauptLieferant))

I believe this should do the trick. Let me know if it works for you.

 

Regards,

Rohan.

View solution in original post

2 Replies
Rohan
Specialist
Specialist

Hi @mruehl ,

Try this once :

=if(dimensionality()=0,
avg(aggr(sum({<%DATEKEY_BESTAND={">=$(=addmonths(vToday,-12))"}>}Bestandsmenge)),%DATEKEY_BESTAND)),
avg(aggr(sum({<%DATEKEY_BESTAND={">=$(=addmonths(vToday,-12))"}>}Bestandsmenge)),%DATEKEY_BESTAND,HauptLieferant))/
Count({<%DATEKEY_BESTAND={">=$(=addmonths(vToday,-12))"}>} Distinct HauptLieferant))

I believe this should do the trick. Let me know if it works for you.

 

Regards,

Rohan.

mruehl
Partner - Specialist
Partner - Specialist
Author

@Rohan 

Thanks a lot, it worked nearly perfect.
Calculating the average not by using the avg()-function did the trick.

I just had to do some small modifications:

if(
dimensionality()=0,
    
sum(aggr(sum({<%DATEKEY_BESTAND={">=$(=addmonths(vToday,-12))"}>}Bestandsmenge),%DATEKEY_BESTAND)),
sum(aggr(sum({<%DATEKEY_BESTAND={">=$(=addmonths(vToday,-12))"}>}Bestandsmenge),%DATEKEY_BESTAND,HauptLieferant))
 
/
 
Count({<%DATEKEY_BESTAND={">=$(=addmonths(vToday,-12))"}>} Distinct [%DATEKEY_BESTAND]))
 
I had to change the count() from supplier to the date dimension. So it now counts the months.
An when I divide by the amount of relevant months, I have to sum up the inventory, not calculting the average.