Skip to Content
0
Former Member
Jun 25, 2009 at 02:47 PM

If Else Condition in Query

24 Views

Hi,

I am trying to use the following condition in my Query:

If Planned Hours = 0 ; then make Actual Hrs = 0 and Over Time = 0

Else OverTime = Actual Hours - Planned Hours.

I created a formula for over time and planned time as below

Lets say the names of Planned hours - Ph, Over time - Ot, Actual Hours - Ah

Formula definition for Actual Hrs:

(Ph==0)0 + (Ph>0)(Ah).

Formula Defination of Overtime :

(Ph==0)0 + (Ph>0)(Ah-Ph) and used before aggregation

But now the issue is that the data is at 0Calday level, the values of Actual Hrs and Palnned hrs correctly at 0Calday when when its at 0calmonth, the value are getting added up and hence getting incorrect values.

For ex.

At 0Calday Level

0Calday Acttime Actime(New) planned hrs Overtime

01/11/2007 8 8 8 0

01/12/2007 8 8 8 0

01/14/2007 3 0 0 0

But when we summarize this at 0Calmonth Level

0Calmonth Acttime Actime(New) planned hrs Overtime

01/2007 19 19 16 3

Please let me know how can we change the aggregation.

Edited by: Mayank on Jun 25, 2009 10:47 AM