topic Conditional Sum in Pivot Table in QlikView

Conditional Sum in Pivot Table

Wed, 04 Apr 2012 23:58:26 GMT

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

Thu, 05 Apr 2012 04:41:20 GMT

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.