Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Conditional Sum in Pivot Table

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.

Tags (2)
1 Reply
Not applicable

Conditional Sum in Pivot Table

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.

Community Browser