Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I was trying to learn today the total qualifier and stumbled across this strange behavior
This is the Data model I'm using :
One Fact Table, and a snow-flaked dimension product.
If I make a chart using the field catégorie (from DimProductCategory) and the expression Sum (SalesAmout) I get this
But if I add to that previous chart an expression based on the TOTAL qualifier - Sum (TOTAL SalesAmount) I get this chart
Basically the use of TOTAL qualifier bring two new values for the field Catégorie (Composant and a null value). The Composant value comes from the dimension DimProductCategory while there is no composant sold in the fact table. The null value, well I just don't understand where it is coming from.
Is it the expected behavior of TOTAL qualifier or did I made a mistake. If it is the expected behavior how can I remove those two fields (with elegance).
Thank you for your help.
Removing blank Category is easy - check "suppress when value is null" on the Dimensions tab.
'Composant' is a different story. You didn't see it in the first table (no total) apparently because "suppress zero-values" is checked on the Presentation tab. If you want to remove it, you need a condition for the "total" column, or maybe better to use calculated dimension in this case, like this:
aggr(if(sum(SalesAmount)>0, Category),Category)
Hi, Total qualifier disregards dimension values, so even if you create a chart with dimensions, the expression won't split values across dimensions, however it will respond to selections,
regards
Removing blank Category is easy - check "suppress when value is null" on the Dimensions tab.
'Composant' is a different story. You didn't see it in the first table (no total) apparently because "suppress zero-values" is checked on the Presentation tab. If you want to remove it, you need a condition for the "total" column, or maybe better to use calculated dimension in this case, like this:
aggr(if(sum(SalesAmount)>0, Category),Category)
Hi,
This is exactly why I used TOTAL to ignore the dimensions displayed in the chart but to take into account the dimensions in the selections.
What I don't understand is why when I add TOTAL to the expression, it displays dimension values that are not linked to any fact (in my model there is not a single fact linked to the value Composant).
Works perfectly
Thank you.
Hi Saad,
This a consequence of the join that QlikView needs to do internally to be able to evaluate the expression. If you can share you QlikView document I could try to give you a more specific answer.
Cheers,
Carlos
Hi,
See the file attached below.