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.
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.
