Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Totals Aren't Matching

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

3 Replies
oknotsen
Master III
Master III

I doubt it is possible for us to help you without seeing the calculations and/or having an idea of your data.

May you live in interesting times!
Not applicable
Author

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

 

))

 

)

 

 

marcus_malinow
Partner - Specialist III
Partner - Specialist III

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