Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
Bit of a conundrum here, I am measuring potential savings for our sites and have worked out some rather complex calcs for them. Our sites are banded by Population Equivalent (PE).
The trouble I'm having is each site has a saving based on it's PE but when I work out the total banded the sum savings aren't matching (figure below bottom figure is right). I suspect this is because it is taking either an average of the PE or some other sum of sites that aren't affecting it.
Is there a way, most likely using the aggregate function by Source Name, that I can make the top total match the bottom?
I'd like to avoid dipping into the calcs if possible as they are very long and convoluted and due to confidentiality I can't post them on here.
Kind regards,
Chris
I doubt it is possible for us to help you without seeing the calculations and/or having an idea of your data.
Hi Onno,
I've removed and replaced certain field names but this is the gist of it
sum({$<[Source Name]={"=(
(sum()/(
sum(distinct(Pop))+
if(month([Pickup Date])='3',sum(distinct([Avg Tourist Pop])),0)+
if(month([Pickup Date])='4',sum(distinct([Avg Tourist Pop])),0)+
if(month([Pickup Date])='5',sum(distinct([Avg Tourist Pop])),0)+
if(month([Pickup Date])='6',sum(distinct([Peak Tourist Pop])),0)+
if(month([Pickup Date])='7',sum(distinct([Peak Tourist Pop])),0)+
if(month([Pickup Date])='8',sum(distinct([Peak Tourist Pop])),0)+
if(month([Pickup Date])='9',sum(distinct([Avg Tourist Pop])),0)+
if(month([Pickup Date])='10',sum(distinct([Avg Tourist Pop])),0)))
)>=$(=[Target])"}>} )
*
(1-([Target]/(
(sum({$<[Source Name]={"=(
(sum()/(
sum(distinct(Pop))+
if(month([Pickup Date])='3',sum(distinct([Avg Tourist Pop])),0)+
if(month([Pickup Date])='4',sum(distinct([Avg Tourist Pop])),0)+
if(month([Pickup Date])='5',sum(distinct([Avg Tourist Pop])),0)+
if(month([Pickup Date])='6',sum(distinct([Peak Tourist Pop])),0)+
if(month([Pickup Date])='7',sum(distinct([Peak Tourist Pop])),0)+
if(month([Pickup Date])='8',sum(distinct([Peak Tourist Pop])),0)+
if(month([Pickup Date])='9',sum(distinct([Avg Tourist Pop])),0)+
if(month([Pickup Date])='10',sum(distinct([Avg Tourist Pop])),0)))
)>=$(=[Target])"}>} ))
/
((sum
({$<[Source Name]={"=(
(sum()/(
sum(distinct(Pop))+
if(month([Pickup Date])='3',sum(distinct([Avg Tourist Pop])),0)+
if(month([Pickup Date])='4',sum(distinct([Avg Tourist Pop])),0)+
if(month([Pickup Date])='5',sum(distinct([Avg Tourist Pop])),0)+
if(month([Pickup Date])='6',sum(distinct([Peak Tourist Pop])),0)+
if(month([Pickup Date])='7',sum(distinct([Peak Tourist Pop])),0)+
if(month([Pickup Date])='8',sum(distinct([Peak Tourist Pop])),0)+
if(month([Pickup Date])='9',sum(distinct([Avg Tourist Pop])),0)+
if(month([Pickup Date])='10',sum(distinct([Avg Tourist Pop])),0)))
)>=$(=[Target])"}>}
distinct([Pop]))+
if(month([Pickup Date])='3',avg(distinct([Avg Tourist Pop])),0)+
if(month([Pickup Date])='4',avg(distinct([Avg Tourist Pop])),0)+
if(month([Pickup Date])='5',avg(distinct([Avg Tourist Pop])),0)+
if(month([Pickup Date])='6',avg(distinct([Peak Tourist Pop])),0)+
if(month([Pickup Date])='7',Avg(distinct([Peak Tourist Pop])),0)+
if(month([Pickup Date])='8',avg(distinct([Peak Tourist Pop])),0)+
if(month([Pickup Date])='9',avg(distinct([Avg Tourist Pop])),0)+
if(month([Pickup Date])='10',avg(distinct([Avg Tourist Pop])),0)))
))
)
Christopher,
I'd suggest sharing a copy of your application.
A couple of ideas:
Sum(distinct pop)
This strikes me as one possible source of issues - if there is the same value attached to more than one category in your second table, you'll end up using it twice there, but only once in your top table.
I'd consider splitting out your expression into component parts to trace which element is not adding up.
Marcus