topic Totals not working correctly in QlikView

Totals not working correctly

jamelmessaoud — Wed, 25 Nov 2020 16:16:04 GMT

Hi guys,

I have an expression

((Sum([Suppliers FTL Cost]) + Sum([Suppliers Drop Cost])) -

(if(Sum({<[ContractorType] = {'Base Rate'}>} ContractorRate) = 0,sum([FFW FTL Cost]) - sum([Ad-Hoc FTL Cost]),Sum({<[ContractorType] = {'Base Rate'}>} ContractorRate)) +

if(Sum({<[ContractorType] = {'Drop Rate'}>} ContractorRate) = 0,sum([FFW Drop Cost]) - sum([Ad-Hoc Drop Cost]),Sum({<[ContractorType] = {'Drop Rate'}>} ContractorRate)))) /

(Sum([Suppliers FTL Cost]) + Sum([Suppliers Drop Cost]))

The problem I am facing is that the Total is not calculating correctly.

The calculation should be Total Cost Diff (column) divided by Suppliers Total Cost (column). Then display this as a percentage.

Within the body of the table the calculation is working perfectly but for the total it is completely wrong! This should be doing

2772.49 / 42574.64 = 6.51%

Can anyone shed any light why this is?

Thanks

Re: Totals not working correctly

sunny_talwar — Thu, 26 Apr 2018 12:18:58 GMT

You need to use Sum(Aggr()) on both numerator and denominator expression

Sum(Aggr(NumeratorExpression, YourDimension/s))/Sum(Aggr(DenominatorExpression, YourDimension/s))

Re: Totals not working correctly

jamelmessaoud — Thu, 26 Apr 2018 12:21:48 GMT

Sounds horrible!

Can you help me do that with the expression I have? The dimensions in my table are Contractor and [Delivery Full Postcode] if that helps?

Thanks

Re: Totals not working correctly

jamelmessaoud — Thu, 26 Apr 2018 12:25:37 GMT

Bingo!

Done it. Thanks Sunny

Re: Totals not working correctly

sunny_talwar — Thu, 26 Apr 2018 12:26:13 GMT

Really?

Sum(Aggr(

((Sum([Suppliers FTL Cost]) + Sum([Suppliers Drop Cost])) -

(if(Sum({<[ContractorType] = {'Base Rate'}>} ContractorRate) = 0,sum([FFW FTL Cost]) - sum([Ad-Hoc FTL Cost]),Sum({<[ContractorType] = {'Base Rate'}>} ContractorRate)) +

if(Sum({<[ContractorType] = {'Drop Rate'}>} ContractorRate) = 0,sum([FFW Drop Cost]) - sum([Ad-Hoc Drop Cost]),Sum({<[ContractorType] = {'Drop Rate'}>} ContractorRate))))

,Contractor, [Delivery Full Postcode]))

Sum(Aggr(

(Sum([Suppliers FTL Cost]) + Sum([Suppliers Drop Cost]))

,Contractor, [Delivery Full Postcode]))

Re: Totals not working correctly

sunny_talwar — Thu, 26 Apr 2018 12:26:30 GMT

Lol okay

Re: Totals not working correctly

jamelmessaoud — Thu, 26 Apr 2018 12:28:35 GMT

Thanks for your help again. Don't know what I would do without this community/yourself !