Hi all,
I met a problem when I added a new expression of running total into the pivot table.
At first, there was only one expression to calculate 'Uninvoiced_Amount' by dimensions of year, month, state, program and subprogram.
And the zero values of expression have been suppressed.
--expression of 'Uninvoiced amount' =sum(if(name<>'Invoiced' and name<>'Cancelled' and year(finishTimeStamp)=PO_DocDate_Year
and Month(finishTimeStamp)=PO_DocDate_Month, PO_NetAmount))
But when I added a new expression to calculate Running Total , the suppressed zero values all showed up. In order to make table neat, I used IF function in my Running Total expression :
IF(Uninvoiced_Amount>0, rangesum(Above(TOTAL sum(if(name<>'Invoiced' and name<>'Cancelled' and year(finishTimeStamp)=PO_DocDate_Year
and Month(finishTimeStamp)=PO_DocDate_Month, PO_NetAmount)),0,RowNo(TOTAL))))
Now the table look like below.
I think maybe that's because I use ROWNO() in the new expression let the zero values show up. And I already checked the 'Suppress zero values' under the new expression but it didn't work. I appreciate some one could help me remove those redundant zero values. Thanks