topic Re: If then expressions in QlikView

If then expressions

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: If then expressions

vishsaggi — Thu, 02 Mar 2017 22:00:34 GMT

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

Re: If then expressions

Thu, 02 Mar 2017 22:16:15 GMT

still giving me no values:

Re: If then expressions

vishsaggi — Thu, 02 Mar 2017 22:18:26 GMT

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

Re: If then expressions

tyagishaila — Fri, 03 Mar 2017 07:01:25 GMT

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.

Re: If then expressions

jonathandienst — Fri, 03 Mar 2017 07:14:55 GMT

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

Re: If then expressions

sasiparupudi1 — Fri, 03 Mar 2017 07:51:49 GMT

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

)

Re: If then expressions

Fri, 03 Mar 2017 13:55:42 GMT

thank you