topic Re: When should I use Aggr in combination with TOTAL? in App Development

When should I use Aggr in combination with TOTAL?

etiennesan — Wed, 08 Sep 2021 08:33:28 GMT

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!

Re: When should I use Aggr in combination with TOTAL?

tresB — Wed, 08 Sep 2021 10:14:48 GMT

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

Re: When should I use Aggr in combination with TOTAL?

etiennesan — Thu, 09 Sep 2021 00:12:46 GMT

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