Conditional Sum in Pivot Table

[#1]

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.

[#2]

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.