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!

       

      -HP

        • 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])})}

           

           

           

          Thanks,

          Sihn

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