
1. Re: NEED HELP with iterating previous value calculations!!!
Yuriy Fal Feb 11, 2017 1:24 AM (in response to SAM CHUA)2 of 2 people found this helpfulHi 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. Re: NEED HELP with iterating previous value calculations!!!
SAM CHUA Feb 11, 2017 9:54 PM (in response to Yuriy Fal)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?
Appreciate all your help!

3. Re: NEED HELP with iterating previous value calculations!!!
SAM CHUA Feb 11, 2017 11:36 PM (in response to Yuriy Fal)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 (20162015=1)
For example, if it is year 2017, the calculation has to be done TWICE to obtain the value (20172015=2)
For example, if it is year 2018, the calculation has to be done THRICE to obtain the value (20182015=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!!!
Yuriy Fal Feb 12, 2017 2:46 AM (in response to SAM CHUA)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!!!
SAM CHUA Feb 12, 2017 4:06 AM (in response to Yuriy Fal)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!!!
SAM CHUA Feb 12, 2017 4:06 AM (in response to SAM CHUA)Apologies, please refer to the attached workbook

Book1.twbx 253.0 KB


7. Re: NEED HELP with iterating previous value calculations!!!
Yuriy Fal Feb 12, 2017 6:22 AM (in response to SAM CHUA)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!!!
SAM CHUA Feb 12, 2017 8:04 AM (in response to Yuriy Fal)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)
(1current 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 (152.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!!!
Yuriy Fal Feb 12, 2017 9:19 AM (in response to SAM CHUA)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%.
Please confirm.
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!!!
Yuriy Fal Feb 12, 2017 9:36 AM (in response to SAM CHUA)Hi SAM,
Please find the attached mockup
with Table Calc Filter on Sheet 7.
Yours,
Yuri

Book1_YF.twbx 407.0 KB


11. Re: NEED HELP with iterating previous value calculations!!!
SAM CHUA Feb 12, 2017 10:03 AM (in response to Yuriy Fal)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!!!
Yuriy Fal Feb 12, 2017 11:07 AM (in response to SAM CHUA)Hi SAM,
Glad I could help.
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
precalculated (materialised) in Excel / CSV / R etc.
The same as using Tableau for calculations,
then exporting (materialise) the resulting dataset(s)
and reusing 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.

Book_YF.twbx 507.4 KB


13. Re: NEED HELP with iterating previous value calculations!!!
SAM CHUA Feb 13, 2017 1:21 AM (in response to Yuriy Fal)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
Uttar Pradesh 0.706
Total: 1.272 (0.566+0.706) (State level calculation)
Rather than
2015
West Bengal 0.566
Uttar Pradesh 0.706
Total: 0.652 (Overall calculation)
Thanks!

14. Re: NEED HELP with iterating previous value calculations!!!
Yuriy Fal Feb 13, 2017 3:41 AM (in response to SAM CHUA)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 TableCalcfiltered 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 workaround 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.
Please find the attached.
Yours,
Yuri

Book_with_Custom_GT_YF.twbx 545.5 KB
