1 Reply Latest reply: Apr 5, 2012 12:41 AM by kevin cool RSS

    Conditional Sum in Pivot Table

    Heri Zhang

      Hi all,

       

      Please check attached file. In this case, I want to sum 'VALUE' by 'STATUS'. But the condition is check the STATUS in last year. If the status is 'IF', sum all the value (IF+TM) with the same ID, but if last year STATUS is 'TM', just sum value where the STATUS  is 'TM' in the same ID. I want to use setting analysis in the expression. Can you show me the expression? Thanks before.

        • Conditional Sum in Pivot Table
          kevin cool

          one way to do it is,create one more column which has status of last year.Then u can use if(status_LY='IF,sum({<status={'IF','TM}>}value,if(status_LY='TM,sum({<status={'TM'}>}value)

           

          to create  status of last year in script ,u can use previous or peek function but first u need to arrange your data by year ascending and by id.the syntax can be:  if(id=peek(id),peek(status),0) as status_LY.