1 2 Previous Next 19 Replies Latest reply on Feb 13, 2017 8:07 AM by Yuriy Fal

# NEED HELP with iterating previous value calculations!!!

Hi guys,

I was able to calculate a table based on a previous value calculation, which relies heavily on the previous value, starting from 2015 all the way to 2034. However, if I want to just select 2023, I will be unable to obtain the correct value.

Below is my calculation

____________________________________________________________

if first()==0 then

sum([2015 Consumers (HH '000)])/sum([Catchment ('000)])

else

if PREVIOUS_VALUE(0)>1 then PREVIOUS_VALUE(0) else

PREVIOUS_VALUE(0)+(1-PREVIOUS_VALUE(0))/

(if PREVIOUS_VALUE(0)<0.5 then [Low Penetration]

elseif PREVIOUS_VALUE(0)<0.6 then [Below Average Penetration]

elseif PREVIOUS_VALUE(0)<0.75 then [Average Penetration]

elseif PREVIOUS_VALUE(0)<0.85 then [High Penetration]

else 1 end)

end

____________________________________________________________

This is the table that I have achieved

If I select the year 2023, the values are wrong because they are no longer based on the initial value in 2015.

How do I change the calculation such that it will iterate the calculation based on the number of years??

Any advice or solutions would be greatly appreciated!!

Thank you!!!

• ###### 1. Re: NEED HELP with iterating previous value calculations!!!

Hi SAM,

You'd like to have a Table Calculation Filter on Years, like:

LOOKUP(MIN(YEAR([Years])),0)

Compute using --> Cell

Table Calculation Filters apply

at the very end of a calc/filter pipeline,

and they "hide" Marks --  not filtering them out --

so the results of other Table Calcs remain intact.

Yours,

Yuri

2 of 2 people found this helpful
• ###### 2. Re: NEED HELP with iterating previous value calculations!!!

Hi Yuriy Fal,

Thanks for the response. But I am still unable to achieve the desired results! In fact, I would prefer if the calculation is not pegged to a table calculation, instead, the values are calculated on the row level (while subjected to the initial value of 2015 and the parameters).

Could you show me an example?

• ###### 3. Re: NEED HELP with iterating previous value calculations!!!

Hi Yuriy Fal,

Apologies, maybe to further clarify would it be possible to iterate the following calculation based on the number of years? It is dependent on the previous value.

Column calculation: Penetration Rate

Based on the initial value in 2015?

For example, if it is year 2016, the calculation has to be ONCE TWICE to obtain the value (2016-2015=1)

For example, if it is year 2017, the calculation has to be done TWICE to obtain the value (2017-2015=2)

For example, if it is year 2018, the calculation has to be done THRICE to obtain the value (2018-2015=3)

To add on, filtering the years will not limit the calculation method. That means if i filter to 2018 only, the calculation method still applies!

Hope this clarifies the problem

Thanks!

• ###### 4. Re: NEED HELP with iterating previous value calculations!!!

Hi SAM,

I couldn't help further without the actual data.

Could you please make / share a workbook?

Yours,

Yuri

• ###### 5. Re: NEED HELP with iterating previous value calculations!!!

Hi Yuriy Fal,

Kindly refer to the attached workbook.

Please refer to the Penetration column.

Thank you.

• ###### 6. Re: NEED HELP with iterating previous value calculations!!!

Apologies, please refer to the attached workbook

• ###### 7. Re: NEED HELP with iterating previous value calculations!!!

Hi SAM,

Thanks for the workbook.

One question about the [Penetration] calc logic:

what is the (common) sense of the four Parameters

and their values? They are currently set as

[Low Penetration] = 3

[Below Average Penetration] = 6

[Average Penetration] = 4

[High Penetration] = 3

else 1

And if possible, could you please shed the light on

what values are you expecting to get at the end?

Pick just one State and some years, please.

It helps me understanding the data more.

Yours,

Yuri

• ###### 8. Re: NEED HELP with iterating previous value calculations!!!

Hi Yuriy Fal,

Thanks for the quick response.

I am trying to model some product consumption (defined as catchment) pattern in India.

For this, I wanted to include the penetration rate of the product. For example, 1 would indicate 100% penetration while 0.3 would indicate 30% penetration.

The rate in which the penetration factor increases is based on its value.

For example, for penetration less than 50%, it would take 3 years to achieve full penetration (Low penetration case)

(1-current penetration)/7 + current penetration for the subsequent year.

For penetration less than 60%, it would take 6 years to achieve full penetration (Below average penetration case) . The calculation is the same.

Hence, the subsequent penetration value is always dependent on the previous value and also the parameter input.

For simplicity sake, we can focus on one state, West Bengal, which has a penetration rate of 52.2% in 2015. In 2016, the penetration rate should be (1-52.2)/7 +52.2 = 62.8%

The parameters are there for me to perform what if scenarios.

Hope the above explanation helps!!!

• ###### 9. Re: NEED HELP with iterating previous value calculations!!!

Hi SAM,

West Bengal, which has a penetration rate of 52.2%

From your Sheet 3 it looks like the number

for West Bengal for 2015 is 56.6%.

And I'm still missing a point about Parameter values.

Does it correct that for the Low Penetration it takes 3 years to saturate,

and for the Below Average Penetration it takes two times more (6 years)?

I don't get it, really ;-) Or the values are set voluntary.

Yours,

Yuri

• ###### 10. Re: NEED HELP with iterating previous value calculations!!!

Hi SAM,

with Table Calc Filter on Sheet 7.

Yours,

Yuri

• ###### 11. Re: NEED HELP with iterating previous value calculations!!!

Hi Yuri,

Yes West Bengal was 56.6% I stated wrongly. Apologies!

For the low Penetration Parameter, it will take 7 years for the market to saturate/mature. (value < 50%)

For the below average Penetration Parameter, it will take 6 years for the market to saturate/mature. (50% < value < 60%)

For the average Penetration Parameter, it will take 4 years for the market to saturate/mature. (60% < value < 75%)

For the high Penetration Parameter, it will take 3 years for the market to saturate/mature. (75% < value < 85%)

While for values > 85% it will take 1 year for the  market to saturate/mature.

These values would determine the growth rate of penetration (based on the calculation).

I included parameters for flexibility to change the market rate of maturity.

I have seen your solution and it solved my initial request! GREAT THANKS!

But would like to explore is it possible for this calculation to be a typical calculation field instead of a table calculation? I would love to use a map to show the penetration rate of these areas and able to filter it by years to see how some states progress faster than others. Likewise, I want to use the penetration value for other calculation purposes.

Many thanks for your help so far!

• ###### 12. Re: NEED HELP with iterating previous value calculations!!!

Hi SAM,

As for your other questions above,

you're using a recursive calculation --

the one referring to itself --

and the PREVIOUS_VALUE()

is the only calc of that type in Tableau.

So one outta use Table Calculations

in the subsequent (nested) calcs

referring to the one(s) containing

PREVIOUS_VALUE() based logic.

The alternatives are at least as complicated.

One would use LEAD / LAG Window functions --

if they're supported in the underlying database SQL dialect.

It does imply using Custom SQL, but at least it is an option

to send Parameters to the Custom SQL based datasource.

Or one could have all possible combinations of model values

pre-calculated (materialised) in Excel / CSV / R etc.

The same as using Tableau for calculations,

then exporting (materialise) the resulting dataset(s)

and re-using them in Tableau as a new datasource.

Yours,

Yuri

PS Mapping Penetration rate by State / Year

is as easy as any other metric. In the attached.

• ###### 13. Re: NEED HELP with iterating previous value calculations!!!

Hi Yuriy Fal,

Thanks for your advice and help! I managed to work around this! Couldn't have done it without your help!

Is there any way I can aggregate the penetration values at the state level and then summing it up (because each state has different penetration value)

What I want!

2015

West Bengal     0.566

Total:                1.272 (0.566+0.706) (State level calculation)

Rather than

2015

West Bengal     0.566

Total:                0.652 (Overall calculation)

Thanks!

• ###### 14. Re: NEED HELP with iterating previous value calculations!!!

Hi SAM,

The first part of your last request is easy to get --

if only one used to be familiar with Custom Grand Totals,

mainly after reading & exercising the foundational article

by Jonathan Drummey (which I've referred to a lot since):

Customizing Grand Totals – Part 2 | Drawing with Numbers

For your particular situation, the (second) part of the article -- the one titled

Customizing Grand Totals with Table Calculations -- could greatly help.

Please find the complimentary Sheet 7 Custom GT in the attached workbook.

As for the second part of your request (as I understand it) --

to show only the choosen Year together with it's Totals --

the complications arise, mainly due to the Tableau behaviour of

showing Custom Grand Total marks for Table Calculation fields

regardless of whether they're been TableCalc-filtered or not.

In this particular case, "hiding" Marks by using LOOKUP(ATTR([Year]),0)

Table Calc Filter doesn't affect the visibility of the GT Marks.

So one need to work-around this.

The trick I've used (as seen in the Sheet 7 Year MAX GT in the attached)

is to "mimic" the Grand Total with the Subtotal of the leftmost Dimension Pill in Rows.

This should be the [Year] Blue Pill (to get the correct Subtotals for each Year).

So the {MAX([Year])} calculation -- the FIXED LOD expression -- is in Columns

just to show the proper value from Year Context filter onto the Column Header.

And the Table Calculation Filter (Boolean) just hides all Years except of the latest one.

You may also want to look at this Educational Brain Teaser

for a good deal of trickery (including the one mentioned above):

Educational Brain Teaser: Twisted KPI Table

If it looks rather complicated, it is intentionally so.