topic Re: Problem with column totalling when using If in expressions in QlikView

Problem with column totalling when using If in expressions

Sat, 10 Nov 2012 14:27:39 GMT

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

Re: Problem with column totalling when using If in expressions

Gysbert_Wassenaar — Sat, 10 Nov 2012 14:54:51 GMT

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.

Re: Problem with column totalling when using If in expressions

Sat, 10 Nov 2012 14:57:11 GMT

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

Re: Problem with column totalling when using If in expressions

SunilChauhan — Sat, 10 Nov 2012 15:17:30 GMT

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.

Re: Problem with column totalling when using If in expressions

Sat, 10 Nov 2012 15:41:03 GMT

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

Re: Problem with column totalling when using If in expressions

Gysbert_Wassenaar — Sat, 10 Nov 2012 15:43:15 GMT

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?

Re: Problem with column totalling when using If in expressions

SunilChauhan — Sat, 10 Nov 2012 15:50:51 GMT

no issue

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

and again convert into Pivot table.

hope this helps

Re: Problem with column totalling when using If in expressions

Sat, 10 Nov 2012 15:54:31 GMT

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

Thanks Mate