topic Re: TOTAL Qualifier Behavior in QlikView

TOTAL Qualifier Behavior

Tue, 13 May 2014 16:58:14 GMT

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.

Re: TOTAL Qualifier Behavior

jaimeaguilar — Tue, 13 May 2014 17:23:41 GMT

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

Re: TOTAL Qualifier Behavior

Anonymous — Tue, 13 May 2014 17:39:24 GMT

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)

Re: TOTAL Qualifier Behavior

Wed, 14 May 2014 08:13:46 GMT

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

Re: TOTAL Qualifier Behavior

Wed, 14 May 2014 08:22:39 GMT

Works perfectly

Thank you.

Re: TOTAL Qualifier Behavior

cfz — Wed, 14 May 2014 08:31:50 GMT

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

Re: Re: TOTAL Qualifier Behavior

Wed, 14 May 2014 08:48:11 GMT

Hi,

See the file attached below.