7 Replies Latest reply: Jan 13, 2015 7:20 AM by Sudha E RSS

    Ask: Partial Sum in Pivot Table to Sum Expression with condition

      Hi. I'm qlikview 11 user and I have a problem with my Account Receivable report.

       

      As you can see in the picture below, I want to add a partial sum in Pivot table to OVERPLAFON column which only have a positive value.

      piutang.JPG

      This is the formula for TOTAL and OVERPLAFON column.

      TOTAL = [BLMJDKW] + [BLMJTTEMPO] + [1-14 HARI] + [15-29 HARI] + [>30 HARI] + [UNKNOWN]

      OVERPLAFON = [TOTAL] - [PLAFON].

       

      IF I just Sum it all (both positive and negative value in OVERPLAFON column), it'll give a not valid value.

      So, that's why I just want to sum a positive value and I'm going to assign 0 to negative value.

       

      I've tried use an condition expression in column OVER, but it doesn't work.

      It always gives 0 value in Partial Sum row.

      I use this expression in OVER column:

      IF ( ((SUM(TOTALRUPIAH)+SUM(RUPIAH))-SUM(NPLAFON)) > 0,

        ((SUM(TOTALRUPIAH)+SUM(RUPIAH))-SUM(NPLAFON)), SUM(0)

      )

       

      NOTE: ((SUM(TOTALRUPIAH)+SUM(RUPIAH)) is equal with TOTAL column and SUM(NPLAFON) is equal with PLAFON column.

      That's a query column name in my mssql server.

       

      With that expression, I can change negative value and replace it with 0 value but it also gives 0 value in Total.

       

      Anyone can help me?

      Hope you guys understand with my poor english ability because I can't speak english well.. hehehe

       

      Thanks