Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

TOTAL Qualifier Behavior

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.

1 Solution

Accepted Solutions
mov
Esteemed Contributor III

Re: TOTAL Qualifier Behavior

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)

6 Replies
jaimeaguilar
Valued Contributor II

Re: TOTAL Qualifier Behavior

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

mov
Esteemed Contributor III

Re: TOTAL Qualifier Behavior

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)

Not applicable

Re: TOTAL Qualifier Behavior

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

Not applicable

Re: TOTAL Qualifier Behavior

Works perfectly

Thank you.

Employee
Employee

Re: TOTAL Qualifier Behavior

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

Not applicable

Re: Re: TOTAL Qualifier Behavior

Hi,

See the file attached below.

Community Browser