8 Replies Latest reply on Feb 22, 2016 1:36 AM by Simon Runc

# PREVIOUS_VALUE() reaching higher than 1 row before

Hey,

Does anyone know if there's a way to self-refer to a table calculation, but reaching more than just one row before. E.g. I need to self -refer my table calculation to a row 10 rows before.

I think that PREVIOUS_VALUE() can be used only to access one row before.

Thanks!

• ###### 1. Re: PREVIOUS_VALUE() reaching higher than 1 row before

hi Thomas,

It'll depend on the shape of your data, level of VizLoD...etc. but you can either use LOOKUP, or WINDOW_SUM/AVG...

For LOOKUP you can define how far back

So for example

LOOKUP(SUM([Sales], -10) brings back the value 10 partitions back

For Window_Sum (or all the WINDOW_XXX calcs) you can define the start and end points

WINDOW_SUM(SUM([Sales]),-10,0) will SUM up the previous 10 partitions back. If you look at my answer here you can see this in action Re: Rolling Months

Hope this helps, but please post back if this a) doesn't make sense b) doesn't solve your issue (in which case a sample of your data would help)

1 of 1 people found this helpful
• ###### 2. Re: PREVIOUS_VALUE() reaching higher than 1 row before

Looks like you beat me to it!...a good use-case for the 'I got this one button'!!!!

1 of 1 people found this helpful
• ###### 3. Re: PREVIOUS_VALUE() reaching higher than 1 row before

Thank both of you guys for your initiative to answer my question. I'm afraid it doesn't solve my problem.

Here is my data (Axis_x_position is a table calculation I want to create, please see the Excel file attached to inspect formulas. There are some different formulas in this column and what I want to do is to pack them into one calculated field, which will be my X axis to draw something like this:

Building barchart treemap (revamping Radial Tree into non-circular shape

). Please mind that LOOKUP() does not allow to make a self-reference which is possible to do using PREVIOUS_VALUE().

 Level Category Size Axis_x_position 1 Category_1 1000 0 2 Subcategory_1a 15 0 2 Subcategory_1b 20 15 2 Subcategory_1c 25 35 2 Subcategory_1d 110 60 2 Subcategory_1e 300 170 3 Sub-subcategory_1ea 50 170 3 Sub-subcategory_1ab 150 220 2 Subcategory_1f 10 170 2 Subcategory_1g 35 180 2 Subcategory_1h 215 215 2 Subcategory_1i 5 430 1 Category_2 300 1000 2 Subcategory_2a 10 1000 2 Subcategory_2b 55 1010 1 Category_3 350 1300 1 Category_4 1020 1650

Hope that my description makes sense!

• ###### 4. Re: PREVIOUS_VALUE() reaching higher than 1 row before

hi Tomek,

...So yes that's a bit of a different problem!

Just so I'm clear, you are trying to re-create the Axis_X_Position, from your Excel but in Tableau? If so what is the 'rule' for when the formula changes from Adding the Previous 2 Rows, To referencing several rows above, to only using the Size from the Previous Row...etc. I ask this as in Tableau we need a 'Rule' as to when a formula needs to do something else.

by way of why...I'll refer you this excellent, and insightful post by Jonathan Drummey, on the differences between Excel and Tableau....

Where this connects to this thread is that there are multiple mental models (paradigm) that people come to Tableau with, here are three:

- "cell-based" that comes from VisiCalc and its descendants, most notably Excel. We effectively operate on one cell at a time and can chain results together.

- "variable-based" that comes from programming. In this model variables are defined and operated over so an operation could be something like "get the value of parameter Alpha, compare it to record 1, if it matches then return X, then compare to record 2, and so on". Writing database application code using cursors is using this paradigm.

- "set-based" that comes from databases. This is where operations are performed "at once" across sets of records, like SUM([Sales]).

As a set based paradigm we can't do things, without a rule-set, to do say C5+J9+K12...etc. as we can in Excel (this is also the reason btw we can run multi-million row calculation is milliseconds, compared with Excel which struggles above 200k rows!!)

1 of 1 people found this helpful
• ###### 5. Re: PREVIOUS_VALUE() reaching higher than 1 row before

Thanks Simon!

Please find attached the Tableau workbook with expected view (showing a structure of flow on website, by categories) and current view which is still far from expected... You can see the formula for axis calculation inside calculated field - I also explained as a comment in calc field, where I would like to use the self-referring (not existing! ) function. Maybe you'll be able to help out with this!

Just as a reminder, this is a continuation of this question Building barchart treemap (revamping Radial Tree into non-circular shape)

Still trying to de-circulate radial tree.

Thanks!

• ###### 6. Re: PREVIOUS_VALUE() reaching higher than 1 row before

Hi Tomek,

Yes that makes sense...and I can see you have the correct values in the Table Version. I'll have a play (might be next week now) and let you know how I get on. One thing that immediately sticks out, from the fact you have the Table Version working, is the 'Order' of the Categories (which you manually sorted for the Table) needs to be same in the Viz (as the PREVIOUS/LOOKUP depend on that order to reference back to the correct row). I changed this in your Viz and immediately got the Level 3 looking right...Level 1 & 2 still looks odd so I'll have a look into why.

1 of 1 people found this helpful
• ###### 7. Re: PREVIOUS_VALUE() reaching higher than 1 row before

Thanks for sharing your ideas. I finally managed to solve this problem and build something I call 'Gantt treemap'. You can see the effect in a workbook attached.

I think this chart is good to show structure or flow, reducing the number of marks displayed (e.g. comparing to Radial Treemap).

1 of 1 people found this helpful
• ###### 8. Re: PREVIOUS_VALUE() reaching higher than 1 row before

Nice work Tomek...and thanks for sharing.

Yes I think this is visually 'easier' to read that the radial plot too...Just got to find a project to 'shoe-horn' this Viz-type into!!