Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
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.