6 Replies Latest reply on Oct 24, 2017 6:53 AM by Zoe Russell

Get Previous Values WITHOUT Using a Table Calculation

Hi all,

I have a workbook that uses Level of Detail Expressions to calculate monthly weighted average investment returns for our clients:

{ INCLUDE [Return Date] : SUM([Return]) } / { INCLUDE [Return Date] : COUNT([Return]) }

I need to compound those returns over time.  This is done using the following equation:

((1 + [Monthly Return 1]) * (1 + [Monthly Return 2]) * ..... * (1 + [Monthly Return N])) - 1

I can do this easily using the Previous_Value function:

PREVIOUS_VALUE(1) * ( 1 + SUM([EW Average Return]) )

However, I really don't want to show the whole table.  I would prefer to just show a consolidated table with the final number.

Is there any way to accomplish this type of calculation without using a Table Calculation?  Any help is appreciated.

I can't share the workbook because it contains confidential info, but if necessary I could create a dummy workbook to post.

Thank you,

Karen

• 1. Re: Get Previous Values WITHOUT Using a Table Calculation

Karen,

Unfortunately, I don't believe this is possible without table calculations.

The good news is that you can create a Table Calc Filter, also sometimes called a "late filter". It filters without interfering with the data your table calculations need. (Think of it as hiding rather than filtering, effectively.)

If you create a table calc:

[Last Entry]

`LAST()=0`

Configure the "compute along" appropriately and set that up on your Filters shelf, you may find that your table calc works just fine, but only the very last entry is displayed.

1 of 1 people found this helpful
• 2. Re: Get Previous Values WITHOUT Using a Table Calculation

Hi Karen, I don't think there's a good way to do this without using a table calculation or some complicated workaround (e.g. in SQL), but one thing you can do is to hide all the columns except the final number. It's pretty simple. Just create a calculated field like this:

LAST() = 0

Put this into your filters, set this to compute along your date dimension, and keep only TRUE.

1 of 1 people found this helpful
• 3. Re: Get Previous Values WITHOUT Using a Table Calculation

You can always anonymize your workbook if you need to share it.

Check out this video:

Anonymize your Tableau Package Data for Sharing

But you may not need to do that here.

You'll have to have the whole table to get your previous values, but if you use a table calc as a filter, it won't filter out the underlying rows.  It will just filter what gets displayed, and your calcs will still have access to the full underlying table that way.

I do this all the time.  LOOKUP() is a common way to get just the row(s) I need to see on the actual sheet.  Also creative use of Index().  If you need me to show you how, then yes, upload something and we can work together.  (Make sure to let me know your Tableau version so I know the right version to open it with.)

In the mean time, here's an old example of using LOOKUP to filter a sheet.  (V 8.2, but it will upgrade to anything you are on.)  It's only one of a million ways you can use table calcs to filter a viz without changing the other table calcs on the sheet.

1 of 1 people found this helpful
• 4. Re: Get Previous Values WITHOUT Using a Table Calculation

Why couldn't you just use a table calculation filter to hide the values for previous months? The calculation for the compounded returns would be unchanged, since the filter would also be a table calculation. You could do this with a filter on the calculated field [Index] : INDEX() such that [Index] = 1 (with the proper sort and compute by settings).

If you really don't want to use table calculations, I would recommend doing your calculations as part of a pre-processing step prior to loading the data into Tableau.

1 of 1 people found this helpful
• 5. Re: Get Previous Values WITHOUT Using a Table Calculation

Hi all,

Thank you so much for your responses.  I tried using a Table Calc LAST() = 0 and it gave me one row with the correct info.  I'll have a lot to expand on to build it out but this is a great solution.

Thanks!

Karen

• 6. Re: Get Previous Values WITHOUT Using a Table Calculation

Hi Karen,

I have a similar issue in so far as I am trying to compound returns within Tableau over filtered date ranges.  I am struggling to get my calculation to match my Excel formula (((1 + [Monthly Return 1]) * (1 + [Monthly Return 2]) * ..... * (1 + [Monthly Return N])) - 1) output.  Would you be able to share your solution?

Thanks!

Zoe