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!!!

    SAM CHUA

      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!!!
          Yuriy Fal

          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!!!
            SAM CHUA

            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

              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!!!
                Yuriy Fal

                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

                  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

                    Apologies, please refer to the attached workbook

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

                      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

                        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!!!
                          Yuriy Fal

                          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

                            Hi SAM,

                             

                            Please find the attached mockup

                            with Table Calc Filter on Sheet 7.

                             

                            Yours,

                            Yuri

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

                              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

                                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

                                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!!!
                                  SAM CHUA

                                  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

                                    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.

                                    Please find the attached.

                                     

                                    Yours,

                                    Yuri

                                    1 2 Previous Next