3 Replies Latest reply: Jun 2, 2016 9:41 AM by Marcus Malinow RSS

    Totals Aren't Matching

    Chris Waterworth

      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

        • Re: Totals Aren't Matching
          Onno van Knotsenburg

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

            • Re: Totals Aren't Matching
              Chris Waterworth

              Hi Onno,

              I've removed and replaced certain field names but this is the gist of it

              sum({$<[Source Name]={"=(

               

              (sum([A])/(

               

              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])"}>} [B])

               

              *

               

              (1-([Target]/(

               

              (sum({$<[Source Name]={"=(

               

              (sum([A])/(

               

              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])"}>} [A]))

               

              /

               

              ((sum

               

              ({$<[Source Name]={"=(

               

              (sum([A])/(

               

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

               

              ))

               

              )

               

               

               

                • Re: Totals Aren't Matching
                  Marcus Malinow

                  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