IIF Query Confirmation

A

Arvin

I'm liv'in the dream ....
Local time
Today, 11:57
Joined
Jul 22, 2008
Messages
192
Hello,

I've battling with this query for awhile now and I think I got it to work but need confirmation that is doing what I want it to do :)

Query is
Code: 
SELECT Sum(IIf([SumOfPlannedHours]=0,"%",([SumOfPlannedHours]/34.15*1)-([SumofPlannedHours] And [PPnRPriorityAreaName]="Admin - Working"))) AS [% of Utilization], Query1.[Employee Name]
FROM Query1
GROUP BY Query1.[Employee Name];

My goal is to sum all planned hours and the subtract planned hours only in
"Admin - Non Working" to get a % Utilization ...

This works ... I just want to know this is actually working the way it should ....

Thank you in advance,

Arvin
 
Okay ....

So I have been playing around with and I have gotten to this point

This formula works:

Code: 
PlannedUtilization:  Sum(IIf([SumOfPlannedHours]=0,"Fixed",IIf([PPnRPriorityAreaName]="Simplify  & Optimize" And "Customer Experience" And "Financial" And "Admin  -  Working", - [SumOfPlannedHours]/34.15*1*-1)))

And the result is 20% ... great ...but when I try this formula

Code: 
PlannedUtilization: Sum(IIf([SumOfPlannedHours]=0,"Fixed",IIf([PPnRPriorityAreaName]="Simplify & Optimize" And "Customer Experience" And "Financial" And "Admin  - Working",- Sum(IIf([SumOfPlannedHours]=0,"Fixed",IIf([PPnRPriorityAreaName]="Admin - Non Working" - [SumOfPlannedHours]/34.15*1*-1)))

I get the error "The expression you have entered has a function containing the wrong arguments."

Based on a excel formula ( which is what I am trying to replicate ... the result should be 25% ....

Excel formula is

Code: 
=IF(U7+U8+U9+U10=0,"0%",SUM(U7:U10)/(((34.15*1)*1)-U11))
 
Re: Sum Iif Query

Just tried the following ..and got the same 20% result ....

Its obviously not the looking at the value i want incorporate in to the formula

Code: 
PlannedUtilization: Sum(NZ(IIf([SumOfPlannedHours],IIf([PPnRPriorityAreaName]="Simplify & Optimize" And "Customer Experience" And "Financial" And "Admin  - Working",-[SumOfPlannedHours]/34.15*1*-1))))-(Sum(NZ(IIf([SumOfPlannedHours],IIf([PPnRPriorityAreaName]="Admin  - Non Working",-[SumOfPlannedHours]/34.15*1*-1)))))

Any assistance is appreciated .....

Thank you in advance
 
1. I don't see how the one can be working as you have something that doesn't make sense in this context.

You have:
PlannedUtilization: Sum(IIf([SumOfPlannedHours]=0,"Fixed",IIf([PPnRPriorityAreaName]="Simplify & Optimize" And "Customer Experience" And "Financial" And "Admin - Working", - [SumOfPlannedHours]/34.15*1*-1)))

Which basically reads as -

1. Take the sum of these
2. If the sum of the planned hours equals zero then
a. return "Fixed" which is a string and something you can't sum.
b.And if it doesn't equal zero then
3. Check these:
([PPnRPriorityAreaName]="Simplify & Optimize" And "Customer Experience" And "Financial" And "Admin - Working"

But that is incorrect syntax. It would need to be:
[PPnRPriorityAreaName]="Simplify & Optimize" OR [PPnRPriorityAreaName]= "Customer Experience" OR [PPnRPriorityAreaName]= "Financial" OR [PPnRPriorityAreaName]= "Admin - Working"

And then you have your value at the end which is negative:
- [SumOfPlannedHours]/34.15*1*-1

But you don't have a final value for if the second IIF returns false.

And it is similar with the second formula.

So, can you restate the exact things that should be in order for you to want the value you want? I'm not sure I'm understanding what it is you are actually looking for.
 
Hi again Bob :)

Basically ... this is what I require ...

If the sum of ([SumOfPlannedHours] in ([PPnRPriorityAreaName]="Simplify & Optimize" And "Customer Experience" And "Financial" And "Admin - Working" is equal to 0 then 0%

if not ...then

sum ([SumOfPlannedHours] In ([PPnRPriorityAreaName]="Simplify & Optimize" And "Customer Experience" And "Financial" And "Admin - Working" and divide it by 34.15 *1 and subtract ([SumOfPlannedHours] In ([PPnRPriorityAreaName]="Admin - Non Working"

this is the best way I can explain it ...
 
Last edited:
The priority area name is what? Is that one value or 4 different values?
 
boblarson said:
The priority area name is what? Is that one value or 4 different values?
Click to expand...


Actually its five diff values .....

Simplify & Optimize Customer Experience Financial Admin - Working Admin - Non Working (not included in % utlization)
 
Arvin said:
Actually its five diff values .....

Simplify & Optimize Customer Experience Financial Admin - Working Admin - Non Working (not included in % utlization)
Click to expand...

So let me see if I understand. For the first test you are wanting to check to see if

[Simplify & Optimize] + [Customer Experience] + [Financial] - [Working Admin] - [Non Working] = 0

is that correct?
 
boblarson said:
So let me see if I understand. For the first test you are wanting to check to see if

[Simplify & Optimize] + [Customer Experience] + [Financial] - [Working Admin] - [Non Working] = 0

is that correct?
Click to expand...

It should be ....

[Simplify & Optimize] + [Customer Experience] + [Financial] + [Admin - Working] - [Admin - Non Working] = 0
 
Just an Fyi ....

based on your earlier post explaining the break of my formula ...I just tried the following and got 702% :confused: fun times ....

Code: 
PlannedUtilization: Sum(IIf([SumOfPlannedHours]=0,0,IIf([PPnRPriorityAreaName]="Simplify & Optimize" Or [PPnRPriorityAreaName]="Customer Experience" Or [PPnRPriorityAreaName]="Financial" Or [PPnRPriorityAreaName]="Admin - Working",[SumOfPlannedHours])))-Sum(IIf([SumOfPlannedHours]=0,0,IIf([PPnRPriorityAreaName]="Admin - Non Working",[SumOfPlannedHours])))/34.15*1*-1
 
Okay, so I think we have come to this:

IIf(Nz([Simplify & Optimize],0) + Nz([Customer Experience],0) + Nz([Financial],0) + Nz([Admin - Working],0) - Nz([Admin - Non Working],0) = 0, 0, [SumOfPlannedHours]/(34.15*1*-1))
 
Hi Bob,

Okay ...so i had to make some changes to it

Now this formula is

Code: 
Sum(IIf(Nz([PPnRPriorityAreaName]="Simplify & Optimize",0)+Nz([PPnRPriorityAreaName]="Customer Experience",0)+Nz([PPnRPriorityAreaName]="Financial",0)+Nz([PPnRPriorityAreaName]="Admin - Working",0)-Nz([PPnRPriorityAreaName]="Admin - Non Working",0)=0,0,([SumOfPlannedHours]/(34.15*1*-1))))

However ... for reason it is not subtracting the time spent on [PPnRPriorityAreaName]="Admin - Non Working",0)

I'm still playing ....
 
I think I know what the issue is ....

How would I subtract the amount time in
[PPnRPriorityAreaName]="Admin - Non Working" from 34.15 and then sum ...

The idea is ...if an employee has planned to be away 1 day (6.83) hours and the remaining time they are working (27.32 Hrs)..their planned utilization still should be at 100% for....
 
Hell yes ! ! ! !:D:D:D:D:D

Got it working !

I had to break up the formula into 3 parts/calculations

1st - Working Time
2nd - Non Working Time
3rd - Utilization

Here is the code ...in SQL View

Code: 
SELECT Sum(IIf(Nz([PPnRPriorityAreaName]="Simplify & Optimize",0)+Nz([PPnRPriorityAreaName]="Customer Experience",0)+Nz([PPnRPriorityAreaName]="Financial",0)+Nz([PPnRPriorityAreaName]="Admin - Working",0)=0,0,([SumOfPlannedHours]))) AS [Working Time], Sum(IIf(Nz([PPnRPriorityAreaName]="Admin - Non Working",0)=0,0,([SumOfPlannedHours]))) AS [Non Working Time], Format([Working Time]/(34.15-[Non Working Time]),"percent") AS ULT, KPIAnalysisSumQry.Weekending
FROM KPIAnalysisSumQry
GROUP BY KPIAnalysisSumQry.Weekending;
 
You must log in or register to reply here.

Similar threads

F
Your Query does not include the specified Expression ‘ID’ as part of an Aggregate Function
Replies
8
Views
588
June7
June7
S
Solved Using a Does Not Contain Criteria in an IIf Statement
Replies
9
Views
338
Pat Hartman
P
D
Return 0 for SUM if no join was possible
Replies
3
Views
299
Pat Hartman
P
X
When was my Worker's Last Day Off?
Replies
3
Views
320
MarkK
MarkK
B
PIVOT Tables
Replies
6
Views
844
June7
June7

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom