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

# COUNTD LOD Expression

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).

Example:

 Employment Status Name Period Date Hours Permanent Emp. A Aug-15 120 Contract Emp. B Aug-15 120 Permanent Emp. C Aug-15 0 Permanent Emp. A Sep-15 110 Contract Emp. B Sep-15 0 Permanent Emp. C Sep-15 120 Permanent Emp. A Oct-15 160 Contract Emp. B Oct-15 160 Permanent Emp. C Oct-15 160

 Period Date Number of Days Working Days Working Hours 2015-08-01 31 20 160 2015-09-01 30 21 168 2015-10-01 31 22 176

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:

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

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?

Dana

• ###### 2. Re: COUNTD LOD Expression

Apologies!

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

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...

countd(

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

then [Name]

end

)

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?

Dana

• ###### 4. Re: COUNTD LOD Expression

Dana,

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:

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

Hi Ryan,

countd(

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

then [Name]

end

)

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 !

Dana

• ###### 6. Re: COUNTD LOD Expression

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

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

You’re welcome… enjoy the weekend!