Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
etiennesan
Contributor III
Contributor III

When should I use Aggr in combination with TOTAL?

Hello everyone,

I have the following situation: I have two dimensions: one being the date, and another a master dimension that is calculated for each products ("SKU"), telling me my inventory status (for example "Overstock").

I want to show for each date, the proportion of products in each inventory status.

I tried doing it with:

 

Count(SKU) / Count(Total<[Date]> SKU)

 

But it was not working. What ended up working was:

 

Count(SKU) / Aggr(NODISTINCT Count(Total<[Date]> SKU), [Date])

 

I don't understand why that Aggr was needed, anyone could please explain? Could it be related to the fact that my other dimension is a calculated one, also using Aggr? 

Thank you very much!

2 Replies
tresesco
MVP
MVP


@etiennesan wrote:

....I don't understand why that Aggr was needed, anyone could please explain? Could it be related to the fact that my other dimension is a calculated one, also using Aggr? 

Thank you very much!


You are right. When you are using two dimensions in the chart  and using  'total <Date>' - your calculation is still respecting the second dimension. However, when you are using 'aggr( ... total <Date> .. , Date)' , you are explicitly mentioning to consider the Date dimension only for aggregation; and therefore - your second dimension in the chart is disregarded in the calculation part.

etiennesan
Contributor III
Contributor III
Author

Thank you for your reply!

However if I use Aggr, then wouldn't I get the same result without writing the details after total? For example:

Aggr(NODISTINCT Count(SKU), [Date])

 

Also, if when you are using two dimensions in the chart, the calculation is still respecting the second dimension, I don't understand how the first reply in the below thread could have worked?

https://community.qlik.com/t5/QlikView-App-Dev/Excluding-one-dimension-from-total/m-p/359992