topic Re: Pivot Table Total in QlikView

Pivot Table Total

seahyanzheng — Thu, 05 Apr 2018 09:12:14 GMT

Hi Guys,

My pivot table does not seems not sum properly for MTD Target and Target:

The calculation for MTD target is:

=if([Sales Group] = 'HAS',

if(GetSelectedCount([Sales Team 1])>0,

sum(aggr((sum({<[Sales Role]={'*'}-{'Team Manager'}, [Day Num]=>} [Ind Target Amt])/1000)*$(vTimeGone),[Sales Group],[Sales Team 1])),

sum(aggr((sum({<[Sales Role]={'*'}-{'Team Manager'}, [Day Num]=, %ProcessingType={'*'}-{'RM Referral'}>} TargetAmt)/1000)*$(vTimeGone),[Sales Group],[Sales Team 1]))),

sum(aggr((avg({<[Sales Role]={'*'}-{'Team Manager'}, [Day Num]=>} [Branch Target Amt])/1000)*$(vTimeGone),[Sales Region],[Sales Team 2]))

)

and if i switched the first if condition to:

=if([Sales Group] <> 'Franchise',

if(GetSelectedCount([Sales Team 1])>0,

sum(aggr((sum({<[Sales Role]={'*'}-{'Team Manager'}, [Day Num]=>} [Ind Target Amt])/1000)*$(vTimeGone),[Sales Group],[Sales Team 1])),

sum(aggr((sum({<[Sales Role]={'*'}-{'Team Manager'}, [Day Num]=, %ProcessingType={'*'}-{'RM Referral'}>} TargetAmt)/1000)*$(vTimeGone),[Sales Group],[Sales Team 1]))),

sum(aggr((avg({<[Sales Role]={'*'}-{'Team Manager'}, [Day Num]=>} [Branch Target Amt])/1000)*$(vTimeGone),[Sales Region],[Sales Team 2]))

)

The total will be 66,200 instead of 1760.

May any advise how to get the total instead?

Re: Pivot Table Total

sunny_talwar — Thu, 05 Apr 2018 09:13:46 GMT

look here

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/ChartFunctions/NestedAggregations/sum-of-row…

Re: Pivot Table Total

seahyanzheng — Thu, 05 Apr 2018 09:18:03 GMT

Hi Sunny,

Thanks!

But i did include the aggr function. just that the parameters are different:

sum(aggr((sum({<[Sales Role]={'*'}-{'Team Manager'}, [Day Num]=, %ProcessingType={'*'}-{'RM Referral'}>} TargetAmt)/1000)*$(vTimeGone),[Sales Group],[Sales Team 1]))),

sum(aggr((avg({<[Sales Role]={'*'}-{'Team Manager'}, [Day Num]=>} [Branch Target Amt])/1000)*$(vTimeGone),[Sales Region],[Sales Team 2]))

Is that the reason why?

Re: Pivot Table Total

PrashantSangle — Thu, 05 Apr 2018 09:19:59 GMT

If possible share your app..

Regards,

Re: Pivot Table Total

sunny_talwar — Thu, 05 Apr 2018 11:13:52 GMT

But isn't this your expression?

You need an overarching Aggr() function over the complete expression... something like this

=Sum(Aggr(

if([Sales Group] = 'HAS',

if(GetSelectedCount([Sales Team 1])>0,

sum(aggr((sum({<[Sales Role]={'*'}-{'Team Manager'}, [Day Num]=>} [Ind Target Amt])/1000)*$(vTimeGone),[Sales Group],[Sales Team 1])),

sum(aggr((sum({<[Sales Role]={'*'}-{'Team Manager'}, [Day Num]=, %ProcessingType={'*'}-{'RM Referral'}>} TargetAmt)/1000)*$(vTimeGone),[Sales Group],[Sales Team 1]))),

sum(aggr((avg({<[Sales Role]={'*'}-{'Team Manager'}, [Day Num]=>} [Branch Target Amt])/1000)*$(vTimeGone),[Sales Region],[Sales Team 2]))

), .....))

Re: Pivot Table Total

seahyanzheng — Fri, 06 Apr 2018 01:09:51 GMT

Thank you very much! Works