topic Re: expression not bringing up right total in QlikView

expression not bringing up right total

rcorcoran — Wed, 28 Feb 2018 09:25:56 GMT

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

Re: expression not bringing up right total

sunny_talwar — Wed, 28 Feb 2018 10:06:20 GMT

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/NestedAggregations/sum-of-row…

Re: expression not bringing up right total

niclaz79 — Wed, 28 Feb 2018 10:28:58 GMT

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.

Re: expression not bringing up right total

sunny_talwar — Wed, 28 Feb 2018 12:07:26 GMT

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

Re: expression not bringing up right total

rcorcoran — Wed, 28 Feb 2018 12:10:10 GMT

No neither working for me

Re: expression not bringing up right total

sunny_talwar — Wed, 28 Feb 2018 12:13:13 GMT

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

Re: expression not bringing up right total

rcorcoran — Wed, 28 Feb 2018 12:22:39 GMT

=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, '')))))

))

Re: expression not bringing up right total

sunny_talwar — Wed, 28 Feb 2018 12:35:10 GMT

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))

Re: expression not bringing up right total

rcorcoran — Wed, 28 Feb 2018 13:30:56 GMT

Thanks Sunny that worked brilliantly.

Re: expression not bringing up right total

sunny_talwar — Wed, 28 Feb 2018 13:32:15 GMT

Super