1 2 Previous Next 17 Replies Latest reply on Jan 17, 2019 10:30 AM by Joe Oppelt

# How to do recursively defined numbers in Tableau?

Hello all,

I am currently working on trying to recreate the attached Excel spreadsheet as a table in Tableau 2018.1 but am running into some problems when trying to implement the same formulas. From my data source, I have the side, shares, price, share balance, and principal but cannot calculate the rest of the columns in Tableau. The three highlighted columns in Excel are the ones giving me trouble in particular. Because these three columns all are dependent on each other and the previous values they held (which is very easy to do in Excel), Tableau throws an error that these three calculated fields are circularly defined and everything just doesn't work. How would I be able to get these fields working in Tableau, and as an extra kicker, be able to do the same table calculation when displaying the data in reverse order.

• ###### 1. Re: How to do recursively defined numbers in Tableau?

I'm not an excel-proficient guy.

Can you supply the equations that define the problematic columns?  (And not in excel-speak.  Spell out what the equations should be.)

And what version of Tableau are you using?

• ###### 2. Re: How to do recursively defined numbers in Tableau?

Here are the three columns giving me problems. For cost, if side is 1, than we want cost to equal principal. If side is -1, then we want it to be shares*side*the previous average cost. For cost avg, we want it to be cost balance/share balance (which is a running sum of shares*side). Cost balance is the running sum of cost. The version of Tableau I am running is 2018.1.

• ###### 3. Re: How to do recursively defined numbers in Tableau?

The error for all three fields is circular reference.

• ###### 4. Re: How to do recursively defined numbers in Tableau?

Have you included an INDEX dimension in your load data that could help order the entries and maintain the first entry as a blank (or zeroes)?

• ###### 5. Re: How to do recursively defined numbers in Tableau?

I don't see any dimension that keeps your rows separate, so in the attached, (V10.5) I created a dummy dimension called [ID].

When you don't have the rows separated, Tableau will sum up things in ways you are not expecting.

See attached.  I made my calc for your [Cost] value.

• ###### 6. Re: How to do recursively defined numbers in Tableau?

I haven't tried that, how would I do that specifically and how does it help the circular error?

• ###### 7. Re: How to do recursively defined numbers in Tableau?

INDEX won't separate data source rows that don't have a dimension value to make then separate.  Even if you just added a sequential record number in a separate column in the data source, that would take care of it.

• ###### 8. Re: How to do recursively defined numbers in Tableau?

This is the calculation for cost that I want, but I don't have cost balance and avg cost in my actual data source, the problem is that I am trying to calculate all 3 columns in Tableau, not just cost when they are circularly defined. I do have a row separator (date) that is ordering my rows in tableau, I just forgot to include that in screenshots.

• ###### 9. Re: How to do recursively defined numbers in Tableau?

I do have a dimension (date) that orders rows already, sorry I didn't understand what you meant at first.

• ###### 10. Re: How to do recursively defined numbers in Tableau?

Your calc for [Cost Avg] ...

Should that be the sum of all [Cost Balance] across the data set divided by the sum of all [Share Balance] across the data set?  Or should it be calculated on a row-by-row basis?

• ###### 11. Re: How to do recursively defined numbers in Tableau?

Cost Avg should be row by row.

• ###### 12. Re: How to do recursively defined numbers in Tableau?

Here is the updated workbook.

I created all three calcs.  If you have them separated by date (which I don't have from the excel sheet you uploaded) then you should be able to do exactly what I did.

• ###### 13. Re: How to do recursively defined numbers in Tableau?

I'm sorry I don't think I explained my data source correctly. The Excel sheet is not the actual data source, just a mockup of what the equations for cost, cost balance, cost avg, and the g/ls should be. My actual data source does not have any of those fields just date, side, shares, price, share balance, and principal. I can not recreate what you made in your example as I cannot do the calculations for the cost columns based on cost columns in the data as those do not exist.

• ###### 14. Re: How to do recursively defined numbers in Tableau?
1 2 Previous Next