7 Replies Latest reply on Aug 28, 2015 4:37 AM by Dana Withers

    COUNTD LOD Expression

    Ryan Botha

      Good day to you all,


      I am trying to calculate a value for the % of project time assigned to a list of employees. So if Emp. A has 120 hours of work assigned in a month where 160 man hours are available, the % project work is 120/160 = 75%.


      To add the hours together for each individual is easy. However, to calculate the total number of man hours available is tricky. This is because if the employee is a contractor, and has 0 hours assigned, we would not want to include their man hours into the overall man hours available for that month (as they are contractors, and only come in when required for that month).




      Employment StatusNamePeriod DateHours
      PermanentEmp. AAug-15120
      ContractEmp. BAug-15120
      PermanentEmp. CAug-150
      PermanentEmp. ASep-15110
      ContractEmp. BSep-150
      PermanentEmp. CSep-15120
      PermanentEmp. AOct-15160
      ContractEmp. BOct-15160
      PermanentEmp. COct-15160


      Period DateNumber of DaysWorking DaysWorking Hours


      Aug '15

      The total man hours available = 160 x 3 = 480

      Total project hours = 120+120+0 = 240

      Project % = 240 / 480 = 50%


      Sep '15

      The total man hours available = 168 x 2 = 336 (Contractor has no hours assigned, therefore exclude their manhours available)

      Total project hours = 110+120 = 230

      Project % = 230 / 336 = 68%


      I ultimately want to plot out a graph this this:graph.jpg


      I've tried LOD expressions, which work if the view contains the Employee Status dimension, but adds all three employees when the pill is removed.



      Hope that makes sense!

      Thank you in advance.

        • 1. Re: COUNTD LOD Expression
          Dana Withers

          Hi Ryan,


          From what I can see it works?

          If I remove the Employment Status dimension, I can see the numbers in the selected row remain the same. (though the order of employees is turned around, so B is now in the middle.)




          What is not correct?



          • 2. Re: COUNTD LOD Expression
            Ryan Botha



            If you drop in just the Year and Month in the columns ,and just the Man hours available in the rows, you'll see it defaults back to counting all three employees, regardless of whether the have hours assigned or not. Should have made that clear with an attachment.


            Also, try dropping the name off the rows, and you'll see it default to 3 again.

            • 3. Re: COUNTD LOD Expression
              Dana Withers

              ooooh I get it! Sorry penny dropped ... I think it can be easily resolved by writing your calculation slightly differently.


              You are doing this:

              iif(sum({INCLUDE  [Employment Status],[Name],[Period Date]:  sum([Hours])}) > 0,COUNTD([Name]),0)

              - Calculate at the level of employment status, name, date  the total number of hours

              - If you sum up that total (but you're no longer at that level of granularity) and it is more than 0, give me a distinct count of users.

              However without that level of granularity you are counting for say the first month: 120+120+0 total is bigger than 0 so you get a distinct count of users = 3.

              If noone was available you'd see less than 3, but nothing else.



              If I change your calculation, I think it works...


              if {INCLUDE  [Employment Status],[Name],[Period Date]:  sum([Hours])} > 0

              then [Name]



              What I'm doing is:

              - If at the level of employment status, name, date  the total number of hours is bigger than 0, give me a name (that persons name)

              - Count the distinct number of names returned.

              Since I give the name at the level of detail and only count at a rolled up level, I'm counting Emp A, Emp B, NULL = 2


              That gives me values of Aug and Sept are 2 and Oct is 3.


              If you then do your further calculation - would that work as you would like it to?



              • 4. Re: COUNTD LOD Expression
                Ryan Botha



                Firstly, thank you for your responses. Truly appreciate it!


                Your answer is great! It's amazing how a change in the order of operations effects the outcome. The final piece to this puzzle is shown below:


                question - post 3.jpg


                I'm sure it's a small tweak, and I will try this over the weekend. If you have a solution, I'd be glad to hear it. Otherwise, I'll check in on Monday to report on success/failure


                Thank you!

                • 5. Re: COUNTD LOD Expression
                  Dana Withers

                  Hi Ryan,


                  Minor tweak indeed I think. How about this:


                  if ([Employment Status] = 'Permanent') or ({INCLUDE  [Employment Status],[Name],[Period Date]:  sum([Hours])} > 0)

                  then [Name]




                  So give me a name if they are permanent or they have hours in the month as previously discussed.

                  Then countd the names of all returned.


                  Good luck with the rest in the weekend !



                  • 6. Re: COUNTD LOD Expression
                    Ryan Botha

                    As we say here in South Africa, you biscuit!


                    Thank you very much. So easy when you read it out! Really appreciate the help! Have a great one!

                    • 7. Re: COUNTD LOD Expression
                      Dana Withers

                      Hahaha! I’ve never been a biscuit before – I like it!

                      You’re welcome… enjoy the weekend!