topic Aggr in set expression using Year and Prior Year not working in QlikView

Aggr in set expression using Year and Prior Year not working

nancymarmat — Thu, 05 May 2016 19:58:58 GMT

I have a Pivot table that displays the value (quantity * cost) of inventory based on grades 1-5. I would like to compare two years. I have the following in two different expressions:

sum(aggr(sum({$< Year={$(=Only(Year))}>}PieceLength*TRC),GradeDesc, LinkItem, MO, Lot)) this works

and

sum(aggr(sum({$< Year={$(=Only(Year)-1)}>}PieceLength*TRC),GradeDesc, LinkItem, MO, Lot)) this shows all 0s

The first expression returns data for the year in the selection. The second expression returns 0 for each grade.

What is the proper syntax to return values for year -1?

Thank you for your help.

Re: Aggr in set expression using Year and Prior Year not working

sunny_talwar — Thu, 05 May 2016 20:05:52 GMT

Try this:

Sum({$< Year={$(=Only(Year)-1)}>}Aggr(Sum({$< Year={$(=Only(Year)-1)}>}PieceLength*TRC),GradeDesc, LinkItem, MO, Lot))

Re: Aggr in set expression using Year and Prior Year not working

nancymarmat — Thu, 05 May 2016 20:13:08 GMT

Perfect! It works!

Thank you so much!