Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone!
Let's say I want to display the top 10 stores by average order value, and I want to display the share in total sales across the network for each store. This is a simplified description, but it is suitable for reproducing the problem.
I get top 10 stores using aggr function. I also suppress null values. The formula for calculating the total amount of sales should take into account the month, so I put “Month” into angle brackets. But, in this case, the total is calculated not for the entire network, but only for the top 10 stores, although the documentation says that total qualifier disregards all chart dimensions except those listed in angle brackets. If I do not use angle brackets, the total is calculated correctly. If I use angle brackets and do not suppress null values, the total is calculated correctly.
The question is why does total with no angle brackets and suppressed null values work according to the documentation and total with angle brackets and suppressed null values doesn’t? Could anyone shed some light on this?
"Total" Issue
When you created the calculated dimension of the Top 10 stores, the Total ignores all dimensions. Once you include a field inside the angle brackets, the dimensions become relevant. the Total <Month> is not breaking down by the Store_ID, but it is only considering the values for the top 10 stores.
If you created the table that had all stores, but only showed the Top 10 (Using dimension limits in the dimension), you would get a different result, see screenshot below ..
When you created the calculated dimension of the Top 10 stores, the Total ignores all dimensions. Once you include a field inside the angle brackets, the dimensions become relevant. the Total <Month> is not breaking down by the Store_ID, but it is only considering the values for the top 10 stores.
If you created the table that had all stores, but only showed the Top 10 (Using dimension limits in the dimension), you would get a different result, see screenshot below ..
So, is there some kind of inconsistency in the docs, or is this behavior some kind of "feature"?
Based on "The TOTAL qualifier may be followed by a list of one or more field names within angle brackets. These field names should be a subset of the chart dimension variables. In this case, the calculation is made disregarding all chart dimension variables except those listed, that is, one value is returned for each combination of field values in the listed dimension fields.", I thought that total will respect chart dimension only if I specify it in angle brackets.