topic Re: Average and calculated dimensions in App Development

Average and calculated dimensions

ManuelRühl — Thu, 18 Jan 2024 11:15:15 GMT

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.

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

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

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

Where is my mistake? 🤔

Re: Average and calculated dimensions

Rohan — Fri, 19 Jan 2024 11:43:20 GMT

Hi @ManuelRühl ,

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.

Re: Average and calculated dimensions

ManuelRühl — Fri, 19 Jan 2024 12:21:59 GMT

@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.