9 Replies Latest reply on Nov 22, 2016 9:40 AM by Ian Johnson

    Need help with an LOD calc

    Ian Johnson

      My apologies if this is confusing.


      I'm trying to sum the distinct count of result pass (Column 6) by name and course name.


      Registered Courses - Each user is registered for a course (Column 3)


      Count of attempts - They can attempt the course more than 1 time (Column 4)


      Count of result Pass - They can pass the course multiple times if they take it more than once (Column 5)


      Distinct Count of Result Pass - The number of distinct passes per course for the individual learner.(Column 6)


      Here is the level of Detail I'd like to generate Course Name >Name> Distinct count of result pass


      However i do not want to display the NAME field, but as soon as I remove it the Distinct count then changes.


      Let me know if I can explain anything better.


        • 1. Re: Need help with an LOD calc
          David Li

          Hi Ian! To summarize, are you basically trying to figure out how many students have passed a particular course at least once? Try summing this LOD calc:

          { FIXED [Name], [Course Name] : MAX(IIF([Result]="Pass", 1, 0)) }

          I haven't tested this, but I think it should work. Obviously, you'll have to change the "Pass" value if that's not how it appears in your data.


          EDIT: Added Course Name to fix the issue.

          • 2. Re: Need help with an LOD calc
            Joshua Milligan


            I'm not sure that's exactly correct.  That will tell you if a student has passed any course at least once.  You'll want to also use Course Name to define the level of detail.


            But the answer may be simpler.  If you already have everything the way you want it, but just don't want to display the course name, you could use the drop down on the Course Name field and un-check "Show Header".  That will keep it in the view to define the view level of detail, but will hide it from display.


            Hope that helps!


            1 of 1 people found this helpful
            • 3. Re: Need help with an LOD calc
              Ian Johnson

              Thanks for the response David!


              Yes, that's exactly what I'm trying to do. However, I need it to total the Min count of the distinct pass results by Name and by Course Name.


              See column 7.  It is taking the 1 across the courses regardless if they passed or not.  See the first row, the person didn't pass that first course "Understanding the UB-04" as you can see in column 5, but the LOD calc is showing 1 because the LOD calc didn't look for distinct courses.


              is there an additional formula that is needed ?


              • 4. Re: Need help with an LOD calc
                David Li

                Yeah, oops! Joshua has it right, it should also include the course name in addition to the student name.


                Apologies for the mistake--I originally used INCLUDE instead of FIXED, which only would require the student name, but I forgot to add the field after changing my mind. You can try one of these two options:

                { FIXED [Name], [Course Name] : MAX(IIF([Result]="Pass", 1, 0)) }


                { INCLUDE [Name] : MAX(IIF([Result]="Pass", 1, 0)) }

                I think these should both work. The advantage of the second one is that it'll continue working even if you decide to aggregate by different fields.

                • 5. Re: Need help with an LOD calc
                  Ian Johnson

                  Josh, I don't want all the user names.  When I deselect "Show Header" from the names dimension it still shows all the rows for each user.

                  • 6. Re: Need help with an LOD calc
                    Ian Johnson

                    aaaand.... Boom goes the dynamite!


                    Perfect David... both of those work... thanks David Li and Joshua Milligan for your help!

                    • 7. Re: Need help with an LOD calc
                      David Li

                      Happy to help!

                      • 8. Re: Need help with an LOD calc
                        Joshua Milligan



                        You're correct.  I missed that you wanted to hide the names too.  So, it looks like David's solution works for you!


                        Another possibility, just for reference to anyone looking at this thread, is to use a row-level calculation that would give you something to do a distinct count.  Something like:


                        IF [Result] == "pass" THEN [Student Name] END


                        That would give you the student name (if available ID or Key would be even better) if the value was "pass" (and NULL which is not counted in aggregations otherwise) on which you could then do a COUNTD.


                        Best Regards,


                        • 9. Re: Need help with an LOD calc
                          Ian Johnson

                          I confirmed that solution works as well.. thanks!