expression not bringing up right total

[rcorcoran]

Hi I have a pivot table with an expression to bring in a target.  I need to hard code one area within it (the bit underlined) to bring in the figure of 12.  My problem is that it will not add in the figure 12 to the rest of the figures in the partial sums and the sum comes out as 73 instead of 85

if(ReferredYYYY=2018 and SGKey=34 and RDOKey=9,'12',num(AVG({<TargetCode={'PAC6'},TargetReferredYYYY= {$(#=only(ReferredYYYY))},TargetReferredMM={1} >}[AnnualTarget]),format.integer)),

Any help appreciated

[sunny_talwar]

May be try like this

Sum(Aggr(

if(ReferredYYYY=2018 and SGKey=34 and RDOKey=9,'12',num(AVG({<TargetCode={'PAC6'},TargetReferredYYYY= {$(#=only(ReferredYYYY))},TargetReferredMM={1} >}[AnnualTarget]),format.integer)),

YourChartDimensionsHere))

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/ChartFunctions/NestedAgg...

[niclaz79]

Hi,

Try switching between the Dimension Limit total and Expression Total, which can generate different results.

Also, try changing the 'Expression Total' vs 'Sum of Rows' in the Total Mode on Expressions page.

[sunny_talwar]

I don't think the Sum of Rows option is available in a pivot table.

[rcorcoran]

No neither working for me

[sunny_talwar]

What all dimensions do you have? Did you add those dimensions to the Aggr() function?

[rcorcoran]

=AGGR(ONLY({<[PAC KPI]={'PAC1A', 'PAC33', 'PAC1B', 'PAC34', 'PAC6', 'PAC7', 'PAC8', 'PAC35'}>}[PAC Metric Description]), [PAC KPI])

RDODesc2

SGNameUMinusNA

are my three calculated dimensions do I need to put all these in the expression??  The full expression for all the targets is below it is the PAC6 one that I need to get the total working on:

Pick(Match([PAC KPI], 'PAC1A', 'PAC33', 'PAC1B', 'PAC34', 'PAC6', 'PAC7', 'PAC8', 'PAC35'),

if(ReferredYYYY=2018,num(avg({<TargetCode={'PAC1A'},TargetReferredYYYY= {$(#=only(ReferredYYYY))},TargetReferredMM={1} >}[AnnualTarget])/100,format.percentage.integer),

if(ReferredYYYY=2017,' ',

if(ReferredYYYY=2016,num(avg({<TargetCode={'PAC1A'},TargetReferredYYYY= {$(#=only(ReferredYYYY))},TargetReferredMM={1} >}[AnnualTarget]),format.percentage.integer),

if(ReferredYYYY=2015,num(avg({<TargetCode={'PC SP 1'},TargetReferredMM={1},TargetReferredYYYY={2015} >}[AnnualTarget])/100,format.percentage.integer),

if(ReferredYYYY=2014, '',

if(ReferredYYYY=2013, '')))))),

if(ReferredYYYY>=2016,num(sum({<TargetCode={'PAC33'},TargetReferredYYYY= {$(#=only(ReferredYYYY))},TargetReferredMM={1} >}[AnnualTarget]),format.integer),

if(ReferredYYYY=2015,'',

if(ReferredYYYY=2014, '',

if(ReferredYYYY=2013, '')))),

if(ReferredYYYY>2016,num(avg({<TargetCode={'PAC1B'},TargetReferredYYYY= {$(#=only(ReferredYYYY))},TargetReferredMM={1} >}[AnnualTarget])/100,format.percentage.integer),

if(ReferredYYYY=2016,num(avg({<TargetCode={'PAC1B'},TargetReferredYYYY= {$(#=only(ReferredYYYY))},TargetReferredMM={1} >}[AnnualTarget]),format.percentage.integer),

if(ReferredYYYY=2015, '',

if(ReferredYYYY=2014, '',

if(ReferredYYYY=2013, ''))))),

if(ReferredYYYY>2016,num(avg({<TargetCode={'PAC34'},TargetReferredYYYY= {$(#=only(ReferredYYYY))},TargetReferredMM={1} >}[AnnualTarget])/100,format.percentage.integer),

if(ReferredYYYY=2016,num(avg({<TargetCode={'PAC34'},TargetReferredYYYY= {$(#=only(ReferredYYYY))},TargetReferredMM={1} >}[AnnualTarget]),format.percentage.integer),

if(ReferredYYYY=2015,'',

if(ReferredYYYY=2014, '',

if(ReferredYYYY=2013, ''))))),

//total not working for this one

if(ReferredYYYY=2018 and SGKey=34 and RDOKey=9,'12',num(SUM({<TargetCode={'PAC6'},TargetReferredYYYY= {$(#=only(ReferredYYYY))},TargetReferredMM={1} >}[AnnualTarget]),format.integer)),

if(ReferredYYYY>=2016,num(sum({<TargetCode={'PAC7'},TargetReferredYYYY= {$(#=only(ReferredYYYY))},TargetReferredMM={1} >}[AnnualTarget]),format.integer),

if(ReferredYYYY=2015,num(sum({<TargetCode={'PC SP 3'},TargetReferredMM={1},TargetReferredYYYY={2015} >}[AnnualTarget]),format.integer),

if(ReferredYYYY=2014, '',

if(ReferredYYYY=2013, '')))),

if(ReferredYYYY>=2016,num(Sum({<TargetCode={'PAC8'},TargetReferredYYYY= {$(#=only(ReferredYYYY))},TargetReferredMM={1} >}[AnnualTarget]),format.integer),

if(ReferredYYYY=2015,num(Sum({<TargetCode={'PC SP 4'},TargetReferredMM={1},TargetReferredYYYY={2015} >}[AnnualTarget]),format.integer),

if(ReferredYYYY=2014, '',

if(ReferredYYYY=2013, '')))),

if(ReferredYYYY>2016,num(avg({<TargetCode={'PAC35'},TargetReferredYYYY= {$(#=only(ReferredYYYY))},TargetReferredMM={1} >}[AnnualTarget])/100,format.percentage.integer),

if(ReferredYYYY=2016,num(avg({<TargetCode={'PAC35'},TargetReferredYYYY= {$(#=only(ReferredYYYY))},TargetReferredMM={1} >}[AnnualTarget]),format.percentage.integer),

if(ReferredYYYY=2015,'',

if(ReferredYYYY=2014, '',

if(ReferredYYYY=2013, '')))))

))

[sunny_talwar]

Try this

Sum(Aggr(If(ReferredYYYY = 2018 and SGKey = 34 and RDOKey = 9,'12', Num(Sum({<TargetCode = {'PAC6'}, TargetReferredYYYY = {$(#=only(ReferredYYYY))}, TargetReferredMM = {1}>} [AnnualTarget]), format.integer)),

[PAC KPI], RDODesc2, SGNameUMinusNA))

[rcorcoran]

Thanks Sunny that worked brilliantly. 

[sunny_talwar]

Super