6 Replies Latest reply on Oct 27, 2015 10:42 PM by alex.thumba

# Is it possible for division of current value of a column with previous value in LOD expression?

I am trying to divide current value of a column with its previous value based on date field.

For example for each for each accounts of a customer, I need to perform the formula,

[(1+current month value of  "performance")/ (1+previous month value of "performance")].

I need to do it for each account under each customers for  from 2010 to 2015.

Is it possible in tableau?

• ###### 1. Re: Is it possible for division of current value of a column with previous value in LOD expression?

I suspect the short answer is yes, but to provide the way of doing this will depend on a lot of things. Please set up the situation using Superstore dataset, get as close as you can, then post a packaged workbook. We could be able to show you the way.

Cheers,

--Shawn

• ###### 2. Re: Is it possible for division of current value of a column with previous value in LOD expression?

Thank you Shawn for your interest in solving this. I am attaching sample dataset in the required format.

I have 'as of now performance' for each account for each month.

I would like to calculate monthly performance from that. For the period February 2011, the values should be

A1001=(1+.690)/(1+.460)

A1002=(1+1.210)/(1+1.190)

A1003=(1+1.310)/(1+1.490)

I want to do this calculation in data level for every month of every category.

To explain in more detail, I have extracted  'as of now' performance  for each month for each account for the analysis from database. But I need to build 'monthly performance' from here. the formula below is for building monthly

[(1+current month value of  "performance")/ (1+previous month value of "performance")].At present we did the calculation in SQL  and extracted data again. It would help me if I could do it in tableau level as a calculated field or something.

• ###### 3. Re: Is it possible for division of current value of a column with previous value in LOD expression?

So the total rolled up to month for Feb. 2011 is 3.09414, yes?

• ###### 4. Re: Is it possible for division of current value of a column with previous value in LOD expression?

Since these three accounts are individual, we need to treat them individually. Above equation should be applied to each account separately.

• ###### 5. Re: Is it possible for division of current value of a column with previous value in LOD expression?

OK. So you're going to need to use a Table Calculation to look back to the previous month. Depending on what your 'real' view looks like you may need a straight table calc:

SUM( 1 + [As of Now performance]  )/

LOOKUP( SUM( 1 + [As of Now performance])  , -1 )

Or a FIXED LOD expression wrapped in a Table Calc:

SUM( { FIXED [Month of Period], [Account ID] : 1 + MAX([As of Now performance]) }  )/

LOOKUP( SUM( { FIXED [Month of Period], [Account ID] : 1 + MAX([As of Now performance]) }  ), -1 )

In the attached workbook these two calcs return the same result, but as you add/subtract dimensions they may behave differently. Your Addressing/Partitioning is going to need to be set correctly to get the expected result.

• ###### 6. Re: Is it possible for division of current value of a column with previous value in LOD expression?

Would it be possible to address the previous month value based on date field rather than partition?