6 Replies Latest reply on Oct 22, 2014 12:42 PM by Steve Mayer

    table calculation

    alamelumangai.venkatavaradachari

      Hi,

       

      I am trying to create a cross table (text table) by using the following dataset. I want to create a calculation that will be using the following formula: ComparitiveSales = sales for that month - sales for the5th prior month.

      eg: ComparitiveSales for May = 500-100=400. 100 is the sales for jan.

      ComparitiveSales for June = 600-200=400. etc

      Please suggest what calculation should I be using for this. Any help appreciated.

       

      MonthSales
      Jan-14100
      Feb-14200
      Mar-14300
      Apr-14400
      May-14500
      Jun-14600
      Jul-14700
      Aug-14800
      Sep-14900
      Oct-141000
      Nov-141100
      Dec-141200
        • 1. Re: table calculation
          Steve Mayer

          Alamelumangai -

           

          The key to your calculation is the LOOKUP function, which lets you lookup values from other rows in the partition. I created a calculated field in the attached workbook called Comparative Sales

           

          LOOKUP(SUM([Sales]), 0) - LOOKUP(SUM([Sales]), -4)

           

          This calculated the difference between the current value, offset = 0, and the value at offset -4 (based on your example, you wanted to go from May back to January, which is an offset of -4).

           

          I built an example in the attached workbook & the results look like this:

          Capture.PNG

          Hope this helps,

           

          -Steve

          • 2. Re: table calculation
            alamelumangai.venkatavaradachari

            Thank you Steve.

             

            When I have to use this Comparitive Sales table calculation on a map or something, but still base the calculation off of the month-year, it doesn't seem like this calculation would work. Could you please let me know how to base this calculation off of month-year, but still plot this on a map or another graph which shows the comparative sales by region or some other dimension? right now, when I pull this field on the map, it says null, for all the regions.

            • 3. Re: table calculation
              Steve Mayer

              Please post a packaged workbook with an example use case. I'm not sure I understand exactly what you are trying to do with the map.

              • 4. Re: table calculation
                alamelumangai.venkatavaradachari

                I have attached the workbook:

                 

                Sheet1 (sales):

                State ID, Month, Sales.

                 

                I have calculated Comparitive Sales using some table calculations.

                 

                Sheet1(State):

                StateID, StateName

                 

                First tab plots comparative sales by month, the table calculation work fine.

                 

                Second tab: trying to plot the state from the second dataset while showing comparative sales from the first dataset. I have created a join on State ID. Here all the comparative sales values shows null.

                • 5. Re: table calculation
                  alamelumangai.venkatavaradachari

                  Attached the workbook

                  • 6. Re: table calculation
                    Steve Mayer

                    Alamelumangai -

                    The Comparative sales table calculation worked in the first example because the worksheet included data across multiple months. This made it possible to use the LOOKUP function to compare current month to 3 months ago.

                     

                    Your map example is a different use case - you've taken out the time element (months) and added data blending.

                     

                    I suggest taking smaller steps (maybe build an example without adding mapping & blending in one step). Try building a Viz that shows comparative sales in a line chart for each state, for example.

                     

                    Sorry I can't be of more help on this, but I think having a deeper understanding of how the table calc works in the first example will get you closer to where you want to go.