2 Replies Latest reply on Dec 9, 2016 2:47 AM by Harnish Patel

    Nested LOD

    Harnish Patel

      Hi everyone,


      I have a data set that has differed levels of repetition based on different schedules. I am having trouble setting up the proper nested LOD statement so that my "Risk Adjusted Opportunity (fixed)" field can be diced every which way.


      In particular, what I need to do is {fixed [DRG]: max([Identified Opportunity])}; however there is a facility schedule that is causing these #s to be 5x and I need to add in a {Exlude [Facility]} to the Identified Opportunity field in the calculation about, but am unsure how to group that correctly. I've attached my sample workbook here.


      Thank you!



        • 1. Re: Nested LOD
          Shinichiro Murakami

          Hi Harnish


          I'm afraid that I don't understand your question correctly, but here is my approach.



          [Identified Opportunity (fixed) SM]

          {fixed[DRG]:max({Fixed [DRG],[Facility]: sum([Identified Opportunity])})}






          • 2. Re: Nested LOD
            Harnish Patel

            Thank you for your response. I'm afraid I am still getting too high of a number when cut this view on the Realization Schedule tab.


            For DRG 302 in September, I am expecting a value of ~52k


            The way the Data is Set up, is as follow

            • I have an opportunity identified [Identified Opportunity (fixed)]
            • I risk adjust the opportunity by 7.6% [Risk Adjusted Opportunity (fixed)]
            • Then I lay these out on the first tab to ensure the data is correct
            • Then I begin to apply the ramp up schedule to see how the values are to be spread out over time.
              • There is where I see my first glitch:  [Risk Adjusted Opportunity (fixed)]/% is not adding up
              • If I do the MAX([Risk Adjusted Opportunity (fixed)])/%, that gives me the correct number, but then when I bring in [Facility] into the columns, the numbers are off again.


            My assumption is that this is happening because I have joined the facility weights in to the data set which makes everything 5x higher to begin with. I think that "fixing" needs to occur at [Identified Opportunity (fixed)], where that Number is unique to a DRG then can be chopped by Facility and by date without increasing with the multiplier of how many records it shows up on.


            I hope that helps!?


            Thank you so much for your help!