topic Pivot Table Sum of rows using aggr in QlikView

Pivot Table Sum of rows using aggr

Wed, 25 Mar 2015 08:33:33 GMT

hi i have the following scenario. i wish to add the rows together to show result in the totals.

i have a table whereby i wish to exclude the planning price in the calculatio nand then show the value as the total with the planning price as seen below in a pivot table.

my code .

if planning_price <> 0,

aggr(sum(value),maingroup,dept,opt)

this however still returns the total value of 1500.

can anyone perhaps help me with this?

maingroup dept optionNumber value planning price

Total 500 -> Desired Result

11 1 500 500 200

12 2 1000 1000 0

Best

Brad

Re: Pivot Table Sum of rows using aggr

MK_QSL — Wed, 25 Mar 2015 10:14:52 GMT

Can your provide sample apps or sample data please?

Re: Pivot Table Sum of rows using aggr

sunny_talwar — Wed, 25 Mar 2015 10:21:17 GMT

You just want the total to include value where planning price <> 0, but the your chart would still have values where planning = 0?? Is my interpretation correct here??

Re: Pivot Table Sum of rows using aggr

sunny_talwar — Wed, 25 Mar 2015 10:29:13 GMT

Depending on the output you are looking for you can use one of the following expressions:

1st table: =Sum(Aggr(Sum(If([Planning Price] <> 0, Value)),[Main Group], Dept, [Option Number]))

2nd table: =If(Dimensionality() = 0, Sum(Aggr(Sum(If([Planning Price] <> 0, Value)),[Main Group], Dept, [Option Number])), Aggr(Sum(Value), [Main Group], Dept, [Option Number]))

HTH

Best,

Sunny