6 Replies Latest reply on Mar 25, 2016 10:30 AM by kumar.pediredla

    Need Help with LOD to calculate 12month terms

    kumar.pediredla

      Hi Tableau experts,

       

      I need some help to calculate an LOD which calculates terminations for 12 months.  I need to display and use this in calculations.  for every month, for example for Feb2016, I need to show terminations for the past 12 months.  We could achieve this with table calculations but need to figure out how to do this with LOD because the numbers change drastically if I exclude a quarter or year in between.

       

      In the attached workbook, in the tooltip, Terms (1Y range) is what I need to calculate with LOD and use that LOD to calculate retention which is the formula ---- "03.01_Retention[03] (c_Term 1y) [%][01]" .

       

      I need help with these calculations.  Because I have date filed in the view, I am able to get the terms for a month but not able to sum it up for 12 month with LOD.  any pointers and suggestions are greatly appreciated.

       

      Thanks for all you help and time.

       

      Thanks,

      Kumar.

        • 1. Re: Need Help with LOD to calculate 12month terms
          Takaaki Koseki

          Hi.

           

          I think LOD expressions is not to refer previous value or lookup value or something like that,

          but it is to change or decide calculation level.

          So you should keep using table calculations.

          Instead, you may change the fields adding into filter to the table calculation,

          not to break other table calculation results.

           

          i.e. create calculated field like this and add it into filter.

          LOOKUP(MIN([Rpt Date]),0)
          

           

          If you have any problem with this way, feel free to tell me!

           

          Thanks.

          • 2. Re: Need Help with LOD to calculate 12month terms
            kumar.pediredla

            Hi Takaaki,

             

            My concern here is let us say I exclude Q4 of year 2015,  I get completely different results.  and if I exclude year 2015 all together, then the calculations change drastically.  which is a big concern for us.  I was trying to avoid those errors.

             

            Thanks,

            Kumar.

            • 3. Re: Need Help with LOD to calculate 12month terms
              Takaaki Koseki

              Did you try it???

               

              These are very first value of your workbook.

                 - L1 Company = Check, Rep Date = Jan 2016

              pic_2016-01.png

                 - L1 Company = Check, Rep Date = Feb 2016

              pic_2016-02.png

               

              Here's the result.

              20160323-4-rep.gif

               

              It looks fine for me.

              I misunderstood your situation? If so, please tell me what is trouble for you more concretely.

              Which calculation changed drastically? And what the situation is?

              • 4. Re: Need Help with LOD to calculate 12month terms
                kumar.pediredla

                Hi Takaaki,

                 

                Greatly appreciate your follow up.  Thanks for the suggestions.

                 

                Yes, I tried your solution and it is quite not there yet.  I do not want to use the calculated date as the filter  I want to use the date on the chart to exclude bars from the view.  when I filter by date on the chart, there is no effect of the calculated field on it.  and in this case the total count is not static.  it changes based on my selection.

                 

                Thanks,

                Kumar.

                • 5. Re: Need Help with LOD to calculate 12month terms
                  Takaaki Koseki

                  Hi Kumar.

                   

                  I'm sorry but I can't still figure out what is difference for you between the calculated field and the existing field.

                  Neither, I can't understand what is the total count which you mentioned. (Neither, how does it change.)

                   

                  For example, if you question like...

                      When I exclude Q4 of year 2015

                          Tableau returns XX.X% as a Retention

                          I want YY.Y% as a Retention

                      Because ...

                  Then we will understand more clearly and help more effectively.

                   

                   

                  If you want a good help here, I recommend to read this article once.

                  Posting a Perfect Question

                   

                  And also, when you try to new problem (even if by yourself), I recommend to simplify the workbook at first.

                   

                  Regards, Takaaki.

                  • 6. Re: Need Help with LOD to calculate 12month terms
                    kumar.pediredla

                    Hi Takaaki,

                     

                    I am adding a document with what I am noticing.  hopefully this helps.  My requirement is "retention%" and "Terms (1Y range)" should not change in the scenario's shown in the document.  Please let me know if this document helps you understand my requirement or if I can answer any questions .

                     

                    greatly appreciate your help.

                     

                    Thanks,

                    Kumar.