topic Re: How to use if-statement in Qlikview? in QlikView

How to use if-statement in Qlikview?

Wed, 17 Apr 2013 11:23:17 GMT

I am using in excel very often formula if(iserror(.... How could I use that in Qlikview?

I have a problem that sometimes I don't get any result with my formula because some part is missing and in these cases I would like to use another formula. How can I write that script?

I have following script

=if(Sum(demand*price)=0,Sum(demand*([Beginning Inventory]/[Beginning Inventory kg])),Sum(demand*price))

0 is wrong in this formula.

If price doesn't exist I will not get any result.

In excel I would write this as following

If(iserror(demand*price),([Beginning Inventory]/[Beginning Inventory kg])*demand,(demand*price))

How can I do same thing in QlikView??

Thanks

Janne

Re: How to use if-statement in Qlikview?

nilesh_gangurde — Wed, 17 Apr 2013 13:09:33 GMT

use the below mentioned script :

=if(isnull(price),Sum(demand*([Beginning Inventory]/[Beginning Inventory kg])),Sum(demand*price))

you can use the isnull() function to find out that the "Price" exist or not. and accordingly calculate the results.

-Nilesh

Re: How to use if-statement in Qlikview?

m_woolf — Wed, 17 Apr 2013 13:43:16 GMT

I can't think of a reason that demand*price would produce and error, except that demand might be null.

Try: if(isnull(demand), .....

or maybe

if(len(demand)=0, ....

Re: How to use if-statement in Qlikview?

jagan — Wed, 17 Apr 2013 14:31:55 GMT

Hi,

Try like this

=if(Sum(demand* Alt(price, 0))=0,Sum(demand*([Beginning Inventory]/[Beginning Inventory kg])),Sum(demand*price))

If Price is null, Alt() will return 0.

Hope this helps you.

Regards,

Jagan.

Re: How to use if-statement in Qlikview?

Thu, 18 Apr 2013 08:35:27 GMT

Thanks! the "Alt" function was excatly what I needed.

Many thanks

Janne

Re: How to use if-statement in Qlikview?

Wed, 24 Apr 2013 08:55:27 GMT

Once again thanks for your great advice. However I have now another problem. QlikView doesn't sum this formula properly in my pivot chart.

I have now following formula monster there

if(Sum(demand*alt(price,0))=0,if(sum(demand*alt(average,0))=0,if(alt(demand,0)=0,0,demand),(sum(demand)*average)),Sum(demand*price))

This formula returns values in every case, but in total row the sum is wrong. If I take all values in to excel I can see that total is something else than Qlikview is telling me. How should I modify my formula that the total sum would be calculated in a right way?

Thanks

Regards

Janne