When should I use Aggr in combination with TOTAL?

[etiennesan]

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!

[tresesco]

---

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

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