4 Replies Latest reply on Nov 19, 2018 2:53 PM by Keith McCarthy

    Prior Period LOD Calculation

    Keith McCarthy

      Is it possible to use a LOD calculation that returns a prior period total?  Yes, I know that window calculations can do this, but I want to specifically use a LOD Calculation.

       

      I attached an example workbook.  My goal is that the 'Prior Period Data (LoD)' field will tie to the 'Prior Period Data (WC)' field.

       

      Please know I have tried many approaches, googled, etc. ad nauseum with no success.  Please help!

        • 1. Re: Prior Period LOD Calculation
          Paul Wachtler

          LODs don't work like that.  They move your calculations up or down a level of detail, but they do not access records across tables.

          • 2. Re: Prior Period LOD Calculation
            Jennifer VonHagel

            Hi Keith,

             

            You could do this in a very pre-planned, brute force kind of way. Check out the attached workbook; my calculations are in the folders highlighted below.

             

            I've put all the interim calculations as their own separate fields we can clearly see what's going on. An LOD can return a specific year's sales as offset from an anchor year, but the LOD can only return ONE year's sales.  To get more years, you need more LODs.  The final Prior Period Sales column can layer the LOD calculations so everything shows in a single column, but you have to create the specific number of LOD Year Sales calculations as prior periods you want to show.

             

            The anchor year I've used here is in the parameter "Current Year". You don't have to use a parameter, you could use an LOD to find the max year in the dataset instead and that would also work.

             

            The workbook is 10.5, let me know if you need an earlier version.

             

            Best,

            Jennifer

            • 3. Re: Prior Period LOD Calculation
              swaroop.gantela

              Keith,

               

              I think Paul's right. Rather than ignoring the current period, the EXCLUDE is

              ignoring the entire dimension of [Period] and moving up a level.

               

              Alternatively, how would you feel about left-joining your datasource to itself

              on a calculated field with Period-1 on side and Period on the other (screenshot below)?

               

              Please see workbook v10.5 attached in the Forum Thread:

              Prior Period LOD Calculation

               

              287935prev.png

              • 4. Re: Prior Period LOD Calculation
                Keith McCarthy

                Thank you to everyone who replied.  All the answers were correct in their own way, but Swaroop's answer is an elegant solution.  Thank you again.