TOTAL Qualifier Behavior

[#1]

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.

[jaimeaguilar]

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

[Anonymous · Accepted solution]

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)

[#4]

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

[#5]

Works perfectly

Thank you.

[cfz]

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

[#7]

Hi,

See the file attached below.