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?

[senpradip007]

Please share sample qvw app to help you better.

[avinashelite]

try like this

if( len(([Cust Buy]+[Cust Sell])/([Buy]+[Sell]))=0 , above(expression_name) , ([Cust Buy]+[Cust Sell])/([Buy]+[Sell]))

[tresesco]

Have a look at this blog post: Peek() or Previous() ?

[Digvijay_Singh]

How come row 5 is showing proper value even it has value of [Cust Buy],[Cust Sell],[Buy] and [Sell] is ZERO? IN fact all values are appearing zero in other rows? Might be I am missing something!

[qlikviewforum]

No it is not ZERO it has some very small value.

[qlikviewforum]

Thanks this is what I was planning for.

[jagan]

HI,

If you want this in script try below script

LOAD

*,

If(Len(Trim([% Cust Vol])) = 0, Previous([% Cust Vol]), [% Cust Vol]) AS New_%CustVol

FROM DataSource;

Hope this helps you.

Regards,

jagan.

[qlikviewforum]

Thanks Jagan but [% Cust Vol] cannot be calculated from the script. So is there is any other suggestion?

[qlikviewforum]

Avinash this did not work out. At first I too thought that it will but unfortunately it did not.