When the o/p of current expression is NULL use output of above

[qlikviewforum]

In the below pivot table as you see in the row=6,10 and 23 the value of the % Cust Vol is NULL. So what we prefer to have is whenever the output of the expression is NULL we would like show output of the above expression (i,e) for 6th row=81%,10th row=81% and 23rd row=78%.

Formula,
%Cust Vol= ([Cust Buy]+[Cust Sell])/([Buy]+[Sell])

Since the value of [Cust Buy],[Cust Sell],[Buy] and [Sell] is ZERO the output of 6,10 and 23 is NULL.

So can you please help me out to overcome this issue?

[avinashelite]

Try like this

if( len(trim (([Cust Buy]+[Cust Sell])/([Buy]+[Sell]) ))=0 or (([Cust Buy]+[Cust Sell])/([Buy]+[Sell]) )=null(),

above(TOTAL Expression_NAME) , (([Cust Buy]+[Cust Sell])/([Buy]+[Sell]) ))

[qlikviewforum]

Yes value is setting properly if I use IF block.

[avinashelite]

Try with this expression this should work

if( len(trim (([Cust Buy]+[Cust Sell])/([Buy]+[Sell]) ))=0 or (([Cust Buy]+[Cust Sell])/([Buy]+[Sell]) )=null(),

above(TOTAL Expression_NAME) , (([Cust Buy]+[Cust Sell])/([Buy]+[Sell]) ))

[avinashelite]

did you check the expression ? is it working ??

[qlikviewforum]

Thanks Avinash for your effort.

After using the above I am getting the value of above expression whenever there is NULL. But not the issue here is I am getting extra records which I dont used to get previously(Getting extra LEVEL's). Not sure why I am getting this!

[avinashelite]

please share the screen shots and are you using any calculated dimensions??

[qlikviewforum]

There is no calculated dimensions. It is very difficult to share the screen shot due to security reasons.

[avinashelite]

hmmm check all your conditions once...without the data it will be hard to guess the issue ....

check for the zero and null values in the [Cust Buy] and [Cust Sell]

hope this helps you

[qlikviewforum]

Yes I agree with you. I will check and get back if anything is required.

Thanks for your support.

[avinashelite]

Sure and don't forget to mark the helpful and correct answers