2 Replies Latest reply on Oct 31, 2018 1:33 AM by Per Strid

    Running sum difference

    Per Strid

      Hi

       

      I am trying to calculate the difference between two graph (or table) objects. A simple table calculation, one might think.

      It's a little more complicated since I've created other calculations already. My "app" looks like this:

      Graph_2018-10-30.png

      The running sums are WINDOW_SUM(SUM([Sales to compare]), - [Running months], 0).

      The parameter [Running months] is set to 0 for R1, 2 for R3 and 11 for R12. So what we want is each bar to be the current months sales, the current plus the two months before or the current and eleven before.

      We hide months rather tan filter, since filtering will disturb the first N months in the window.

      So far it works fine.

       

      Now we want to get the difference between Sweden and the country to compare. In my mind this is a LOOKUP.

      [Running Sales to Compare] - LOOKUP([Running Sales to Compare], 1).

       

      At R1 the difference between Sweden (always the reference) ant Austria (the country to compare) for December 2018 would be 3474 - 113 = 3361

      For November 2018; 1734 - 7809 = -6075.

       

      Switching to R3 these numbers  should be 5663 - 12517 = -6854 and 2683 - 14861 = -12178

       

      I don't get this with my LOOKUP.

       

      Does anyone know what I do wrong?

       

      Thanks!

      Pelle

        • 1. Re: Running sum difference
          Jim Dehner

          Hi

           

          see the attached

          I was able to get the values you want - but it will result in a line on each of the country charts

           

          like this

           

          this is the calculation

           

          it is computed as shown

           

           

          then at the difference level

           

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: Running sum difference
            Per Strid

            Thanks Jim!

             

            In real it is a customer of mine who wants to compare their own sales to the two most aggressive competitors (number of sold items). They buy the numbers from an external service.

             

            It was no problem at all to continue fiddle around with labels, colors or new charts.

             

            I could use the formula you wrote to build exactly what I wanted (almost)!

            The only thing I could improve is the scale on the diff. In my mind the scale should be the same as in the real numbers. Here is an image of the result.

            Result.png

            I attache the workbook again with new sheet.

             

            Great thanks Jim!

            Pelle