If then expressions

[#1]

I am trying to do an "if then" expression but have not done one in qlikview before

I currently have the following expression to calculate sales volume variance:

(Sum( {$<Year={$(=Max(Year))}>} Tons) - Sum( {$<Year={$(=Max(Year))}>} PlanTons))*(Sum( {$<Year={$(=Max(Year))}>} PlanBaseSales)/Sum( {$<Year={$(=Max(Year))}>} PlanTons))

I want to create an "if then" that if the above expression = 0, to then display an amount = Sum( {$<Year={$(=Max(Year))}>} BaseSales)

but it keeps bombing out on me.

Can anyone help?

Thanks

[vishsaggi]

Try this?

= IF(((Sum( {$<Year={$(=Max(Year))}>} Tons) -

      Sum( {$<Year={$(=Max(Year))}>} PlanTons)) * (Sum( {$<Year={$(=Max(Year))}>} PlanBaseSales)/Sum( {$<Year={$(=Max(Year))}>} PlanTons))) = 0,

      Sum( {$<Year={$(=Max(Year))}>} BaseSales))

[#3]

still giving me no values:

[vishsaggi]

What is your expected output? Can you share a sample app ?

[tyagishaila]

Suppose expression below has Label Exp

(Sum( {$<Year={$(=Max(Year))}>} Tons) - Sum( {$<Year={$(=Max(Year))}>} PlanTons))*(Sum( {$<Year={$(=Max(Year))}>} PlanBaseSales)/Sum( {$<Year={$(=Max(Year))}>} PlanTons))

now add expression

if(Exp=0,Sum( {$<Year={$(=Max(Year))}>} BaseSales), Exp)

and hide column Exp in presentation Tab.

[jonathandienst]

Rephrasing your requirements, if the first two terms are the same (resulting in 0 for the first expression):

If(Sum({$<Year = {$(=Max(Year))}>} Tons) <> Sum( {$<Year = {$(=Max(Year))}>} PlanTons),

  (Sum({$<Year = {$(=Max(Year))}>} Tons) - Sum( {$<Year = {$(=Max(Year))}>} PlanTons))

  * (Sum({$<Year = {$(=Max(Year))}>} PlanBaseSales) / Sum({$<Year = {$(=Max(Year))}>} PlanTons)),

  Sum({$<Year={$(=Max(Year))}>} BaseSales)

)

That simplifies the problem and reduces the calculation, but does not cover the case where PlanBaseSales = 0. If that needs to be considered as well, you will probably need the whole expression in the If condition. In psuedo-code:

If(<expression 1> <> 0, <expression 1>, <expression 2>)

[sasiparupudi1 · Accepted solution]

If((Sum( {$<Year={$(=Max(Year))}>} Tons) - Sum( {$<Year={$(=Max(Year))}>} PlanTons))*(Sum( {$<Year={$(=Max(Year))}>} PlanBaseSales))=0,

Sum( {$<Year={$(=Max(Year))}>} PlanBaseSales) ,

(Sum( {$<Year={$(=Max(Year))}>} Tons) - Sum( {$<Year={$(=Max(Year))}>} PlanTons))*(Sum( {$<Year={$(=Max(Year))}>} PlanBaseSales)/Sum( {$<Year={$(=Max(Year))}>} PlanTons))

)

[#8]

thank you