8 Replies Latest reply on Oct 19, 2012 10:41 AM by Kevin Sullivan

# Dividing one column row by next column one row down

Is there a way to create a calculated field that would divide the “Sum of Customers” by the “FEC Level” that is one row down?  So for example I want a calculated field that would divide the “Sum of Customers” on level 3 which is 56 by the “FEC Level” 4…..  so 56 divided by 4.  The result 14 would show on Level 3 in line with the 56 number.

FEC Level   Sum of Customers              NEW CALC FIELD WOULD BE LIKE THIS

0                              5                                       5/1 = 5

1                              36                                     36/2 = 18

2                              39                                     39/3 = 13

3                              56                                      56/4 = 16

4                              155                                   and so on

5                              633

6                              785

7                              987

8                              1123

• ###### 1. Re: Dividing one column row by next column one row down

Does the FEC level look exactly like this (increment by 1)? If not, what determines its sort?

• ###### 2. Re: Dividing one column row by next column one row down

no i was just using simple numbers....  it can be any number

• ###### 3. Re: Dividing one column row by next column one row down

Are you therefore relying on the sort that exists in the source, or is there a date/time?

• ###### 4. Re: Dividing one column row by next column one row down

relying on that sort that exists in source

• ###### 5. Re: Dividing one column row by next column one row down

That's going to be tough then because there's no way of explicitly setting it for Tableau. In one view it will look fine, in another it may break. Can you add an index key to your data - even a column increasing by one each row would do it.

It's possible you could do it with custom SQL, but I had no luck trying a count:

SELECT ['Date Buckets\$'].[Bucket Number] AS [Bucket Number],

['Date Buckets\$'].[Bucket Selector] AS [Bucket Selector],

['Date Buckets\$'].[Bucket Size] AS [Bucket Size],

(SELECT COUNT(*)+1 FROM ['Date Buckets\$'] WHERE [Bucket Selector] <= ['Date Buckets\$'].[Bucket Selector]) AS [Sort]

FROM ['Date Buckets\$']

1 of 1 people found this helpful
• ###### 6. Re: Dividing one column row by next column one row down

I think I figured it out!  Thank you for the replies.

LOOKUP((SUM([FEC Level])/(LOOKUP(ZN(SUM([sum of customers])), - 1))),1)

• ###### 7. Re: Dividing one column row by next column one row down

Yep, that will work as long as the sort doesn't change, which it often will depending on the chart/view. That's what I've been trying to work out.

Lookup is reliant on the sort.

1 of 1 people found this helpful
• ###### 8. Re: Dividing one column row by next column one row down

so now I got the calc to work how do i get it to show the Average of the calc on the grand total row?