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?

[qlikviewforum]

Yep sure

[qlikviewforum]

One more thing I forgot to tell. Even though [Cust Buy],[Cust Sell],[Buy] and [Sell] is ZERO the "% Cust Vol" must always be same for Date and CLASS combination. This is the problem statement. Any idea what changes are required now?

So basically based on the Date and CLASS combination the "% Cust Vol" must be same even though  [Cust Buy],[Cust Sell],[Buy] and [Sell] is ZERO. Hope the requirement is understandable? If not please let me know.

[qlikviewforum]

Anyone have any suggestions for this?

[qlikviewforum]

Avinash any advise on this with the requirement I have mentioned recently?

[avinashelite]

As i observe your example is showing properly rite ?? if you need the combination format try with the aggr() function on the DATE and CLASS that should help 

[qlikviewforum]

I did tried AGGR using Date and CLASS but didn't work.

[avinashelite]

need to check the data and how its associated

[qlikviewforum]

Yeah I understood your point but due to security issue I couldn't provide anything.

[qlikviewforum]

Hi Avinash,

Finally I have got the required output using the below code. Thanks for all your help.

Sum(TOTAL<Date,CLASS>

      Aggr(

               ([ORIGINAL EXPRESSION])

      ,Date,CLASS)
      )

[avinashelite]

Great mark the correct and helpful answers and close this thread