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

# table calculation

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.

 Month Sales Jan-14 100 Feb-14 200 Mar-14 300 Apr-14 400 May-14 500 Jun-14 600 Jul-14 700 Aug-14 800 Sep-14 900 Oct-14 1000 Nov-14 1100 Dec-14 1200
• ###### 1. Re: table calculation

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: Hope this helps,

-Steve

• ###### 2. Re: table calculation

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

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

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

Attached the workbook

• ###### 6. Re: table calculation

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.