Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Problem with column totalling when using If in expressions

Hi Folks,

Need some help on a bit of modelling I'm trying to do.  Basically I want to forecast 2nd, 3rd and 4th quarters based on the results of 1st quarter.

I have two variables which are used for the forecast, medicalGrowthFactor and surgicalGrowthFactor.  These need to be applied based on the text in the "Medical/Surgical" field.

The Q1 Actual column is a Sum().

The code to calculate each of the forecast columns is :

If([Medical/Surgical] = 'Medical', //If it's medical

           ([Q1 Actual]+([Q1 Actual]*(medicalGrowthFactor/100))) //Apply medical growth factor

           , If([Medical/Surgical] = 'Surgical', // Else, is it surgical?

            ([Q1 Actual] +([Q1 Actual]*(surgicalGrowthFactor/100))) //Apply surgical growth factor

            , [Q1 Actual])) //Otherwise return Q1 Actual

This works just fine and all the values I get are exactly what I'm looking for, BUT the problem I'm having is for the column totals, using 'Show Partial Sums' in pivot.  The totals all match the Q1 Actual i.e. it's not totalling the values in the column!

If I change the code to below

If([Medical/Surgical] = 'Medical', //If it's medical

           ([Q1 Actual]+([Q1 Actual]*(medicalGrowthFactor/100))) //Apply medical growth factor

           , ([Q1 Actual] +([Q1 Actual]*(surgicalGrowthFactor/100))) //Apply surgical growth factor

          )

The column total shows factors in the surgical growth but not the medical.

Any suggestions??  My brain is melting.....

1 Solution

Accepted Solutions

Re: Problem with column totalling when using If in expressions

Ah, that's sounds like you're using a pivot table. Any reason it must be a pivot table or is a straight table a possibility?


talk is cheap, supply exceeds demand
7 Replies

Re: Problem with column totalling when using If in expressions

Did you already change the Total Mode from expression to Sum of Rows? Expression Total doesn't work if you use expression names (or column references) in your expression.


talk is cheap, supply exceeds demand
Not applicable

Re: Problem with column totalling when using If in expressions

Can't do that as the first column is also an expression.  Thanks for the thought though.

chauhans85
Esteemed Contributor

Re: Problem with column totalling when using If in expressions

while calculating expression using if some time show you not corect result .because you applied suprees null on dimesion. if you remove the supress null check in dimension then it should match.

Not applicable

Re: Problem with column totalling when using If in expressions

Total mode is not changeable for me on the expressions.  It's greyed out.

Re: Problem with column totalling when using If in expressions

Ah, that's sounds like you're using a pivot table. Any reason it must be a pivot table or is a straight table a possibility?


talk is cheap, supply exceeds demand
chauhans85
Esteemed Contributor

Re: Problem with column totalling when using If in expressions

no issue

you can convert first pivot into staight and check total in expression tab

and again convert into Pivot table.

hope this helps

Not applicable

Re: Problem with column totalling when using If in expressions

Great, converted to straight table and changed total method.  All good now.

Thanks Mate

Community Browser