5 Replies Latest reply on Jul 18, 2018 1:56 PM by Scott Schmeling

    LOD formula not working as expected

    Scott Schmeling



      I am trying to work with some LOD formulas to get a Percentage.  Please see attached workbook. 

      Basically I have an Enrollment Date and and Internal Referral Date- and I need to be able to create an Enrollment Percentage as well as be able to filter by a specific location or all locations.


      The crosstab looks like this:


      Enrollment %.58.73
      Internal Referrals5.012.0


      The formula for Enrollment %'s is Enrollments / Internal Referrals. 

      Of course I tried the regular formulas and they did not work due to the categories causing issues.  So then I tried a level of Detail Function:


      Enrolled Fixed:

      {Fixed (DAtePart('month',[Date])), DAtepart('year',[Date]),[Location from]: CountD([Enrolled])}


      Internal REferrals Fixed:

      {Fixed (DAtePart('month',[Date])), DAtepart('year',[Date]),[Location from]: CountD([Internal Referrals])}


      Enrolled % Fixed:

      [Enrolled Fixed]/[Int. Ref Fixed]


      Then I added this to the table with the filter for location but as you can see in the table above the formula is not working.  I believe the issue is somewhere in the aggregation of the Combined Formula:


      Case Attr([Category])

          When "Activation Date" Then CountD([Active])

          When "Enrollment Date" Then AVG([Enrolled % Fixed])

          When "Internal Ref Date" Then CountD([Internal Referrals])

      Else Null



      However I'm not sure what the correct aggregation would be as of course Sum does not work and average does not work and of course it won't accept the AGG().


      Any help would be greatly appreciated.

        • 1. Re: LOD formula not working as expected
          Jim Dehner


          you need to aggregate the fixed values in the % calculation


          the combined then becomes

          and it returns this



          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: LOD formula not working as expected
            Scott Schmeling

            Thanks so much Jim,


            I am not sure why I was having such trouble finding that one.  But yes it works like a charm and will make my life a lot easier going forward.

            • 3. Re: LOD formula not working as expected
              Scott Schmeling

              Hi Jim,


              Not to reopen this discussion but something strange is happening now.  It works great on the small scale like I submitted.  However ran across this issue:


              When I expand it and a location has Referrals for June but no Enrollments- they are not being added in to the total calculation for the %.  It is not what I would expect. 

              For Example.


              Location C- for June has 5 Referrals but no Enrollments.  Those 5 Referrals are not being added to the Denominator for total %. 


              Any suggestions for this?



              • 4. Re: LOD formula not working as expected
                Jim Dehner

                Hi Scott


                I am working on a solution - it has to do with totals and the LOD's but I ran into a road block


                see below - the formula is going to calculate totals and place them in the total column - but I get the wrong answer (.72) for the ratio

                I broke out the numerator and it total 18 (see red circle) - I tried a number of different approaches (LOD's, widow sum) to get to 15 but can't find where it is going wrong


                is there anything in the data that would account for the difference




                • 5. Re: LOD formula not working as expected
                  Scott Schmeling

                  Hi Jim,


                  So what I found in the data is that It seems to miss the locations that don't have any information in the Enrolled Date Column- meaning they are blank. 


                  Orange County Med and San Bernardino Med you can see have Internal Referrals section but no Enrolled % section.  This can only be accounted by the fact that they each have blanks in the data date for the Enrolled section.



                  I'm thinking at this point I may talk to them and say they are too new to report but I don't particularly like that answer either.