2 Replies Latest reply on Nov 1, 2016 2:53 PM by Ian Johnson

    Help with a LOD formula

    Ian Johnson

      I have pasted a sample of my dataset below.


      I will be using a single select dropdown to select the learner name, so there will be only 1 learner selected at a time.  With that being said, I need to create a formula that will return the following:


      If distinct course name has a score that = 0 then Null, 

      If distinct course name has a Satisfaction of "Failed" without any "Passed" then result should be "Open"

      If distinct course name has a Satisfaction of "Failed" but then also has "Passed" then result should be "Closed"


      Course NameNameFirst LaunchCompletion DateTotal Time (minutes)ScoreCompletionSatisfaction
      A/R AnalysisLearner 16/9/2016 11:286/9/2016 11:302.1100%completepassed
      A/R BasicsLearner 15/13/2016 12:105/13/2016 12:2212.390%completepassed
      A/R BasicsLearner 15/13/2016 11:13unknown39.660%incompletefailed
      A/R BasicsLearner 15/13/2016 12:235/13/2016 12:252.290%completepassed
      A/R BasicsLearner 15/13/2016 11:54unknown12.660%incompletefailed
      A/R Transactions and Explanation of BenefitsLearner 16/9/2016 15:276/9/2016 15:4013100%completepassed
      Billing BasicsLearner 15/13/2016 12:07unknown0.40%incompletefailed
      Coordination of BenefitsLearner 16/24/2016 12:596/24/2016 13:1516.482%completepassed
      Fundamentals - BillingLearner 16/30/2016 19:386/30/2016 19:413.5100%completepassed
      Fundamentals - CollectionsLearner 16/30/2016 19:426/30/2016 19:453.2100%completepassed
      Managing DNFB AccountsLearner 15/24/2016 18:27unknown22.650%incompletefailed
      Managing DNFB AccountsLearner 15/24/2016 19:275/24/2016 19:292.380%completepassed
      Managing Late and Missing ChargesLearner 15/24/2016 19:195/24/2016 19:20192%completepassed
      Managing Late and Missing ChargesLearner 15/24/2016 18:51unknown1.50%incompletefailed
      Managing Late and Missing ChargesLearner 15/24/2016 18:535/24/2016 19:1622.792%completepassed
      Managing Late and Missing ChargesLearner 1unknownunknown00%unknownunknown
      Managing Uncollectible AccountsLearner 16/9/2016 13:476/9/2016 14:1023.570%completepassed
      Medicare as Secondary PayerLearner 16/24/2016 13:266/24/2016 13:359.292%completepassed
      Third-Party Follow-Up: Skills for SuccessLearner 16/9/2016 11:076/9/2016 11:2719.973%completepassed
      Third-Party Follow-Up: Skills for SuccessLearner 16/8/2016 18:42unknown68.50%incompletefailed
      Third-Party Payer BillingLearner 15/20/2016 14:135/20/2016 14:3319.670%completepassed
      Understanding the Medicare 3 day Payment WindowLearner 17/8/2016 11:437/8/2016 11:5511.8100%completepassed
      Understanding the UB-04Learner 15/23/2016 11:39unknown2846%incompletefailed
      Understanding the UB-04Learner 15/24/2016 17:48unknown33.877%incompletefailed
        • 1. Re: Help with a LOD formula
          Ivan Young

          Hi Ian,

          Here's how I would approach this problem.


          1:  Create a LOD that will return satisfaction.  We'll call it Max Satisfaction:  { FIXED  [Course Name] : MAX([Satisfaction]) }

               This field will return failed if there are no passed associated with a Course Name.  If there is a single instance of passed associated with that Course Name passed will be returned.


          2. Create the logic that will return what you are looking for.  I called it status.


          IF [Score] = 0 THEN NULL

          ELSEIF [Max Satisfaction] = 'Failed' THEN 'Open'

          ELSEIF [Max Satisfaction] = 'Passed' THEN 'Closed'



          Let me know if you have any questions.





          • 2. Re: Help with a LOD formula
            Ian Johnson

            Hi Ivan, thanks for your help.


            Since I posted that I actually pieced something together that works.  It's not the prettiest thing in the world, but it appears to get the job done.


            I created a custom course name calc to return distinct course names:

            { FIXED [Course Name] : MIN([Course Name])}



            Then I created an initial formula to null all 0% scores,  and assign a "-100" value to all "Passes", and a "1" value to all "failed" attempts:   (I renamed satisfaction Result)

            IF [Score]=0 THEN NULL elseif [Score]>0 AND [Result] ="Failed" THEN 1

            ELSEIF [Score]>0 AND [Result]="Passed" THEN -100 ELSE NULL



            (i used passed = -100 so if they take the course 10 times and fail and pass once we could be sure that it would stand out)



            Then I created another calculation that would assign the status of the course by looking at the grand total.  All of the courses with a positive value would mean that there are no passes and therefore OPEN

            if SUM([Passed -1 / failed 1])>0 THEN "Open" ELSE "Closed"





            I didn't think i'd figure it out.. but I really appreciate your feedback!