Colour highlight rows dependent on conditions/critiera

[#1]

Hi,

I have a straight table that looks as below, and I need to add some conditions and (if possible) highlight the rows Green/Red/Amber dependent on the following criteria. If it is not possible to highlight the rows, perhaps add a new column as 'RAG Status' and have G/R/A inputted depending on the result.

Criteria:

GREEN - Actual Start/End Time is less than 5 minutes of the Scheduled Start/End Time

AMBER - Actual Start/End Time is more than 5 and less than 30 minutes of the Scheduled Start/End Time

RED - Actual Start/End Time is more than 30 minutes of the Scheduled Start/End Time

Table:

Name	Description	Scheduled Start Time	Scheduled End Time	Actual Start Time	Actual End Time
Run1	ABC	07:30:00	07:45:00	07:34:00	07:46:00
Run2	123	18:00:00	18:20:00	18:15:00	18:24:00
Run3	XYZ	09:45:00	10:05:00	09:55:00	10:22:00
Run4	456	12:05:00	12:30:00	12:05:00	13:08:00

So in this instance, the results would be as follows:

Run1 - Green - Times are within the criteria

Run2 - Amber - Starts more than 5 minutes after Scheduled Start Time

Run3 - Amber - Starts and Ends more than 5 minutes after Scheduled End/Start Times

Run4 - Red - Ends more than 30 minutes after Scheduled End Time

Thanks and kind regards,

Tristan

[#2]

Yeah these should all be green. Although I can see a problem with this; the last one should be red as it's started 6 hours late - however the expression is going to see it as starting early as the 00:01:03 is less than 18:00:00.

[sunny_talwar]

to factor in the last one you might want to consider bringing in the date so that the application know if it is next day or not.

I am going to test those times now with the my dummy application.

[sunny_talwar]

I am getting all green, not sure why you got a red for two of them:

[#5]

Your version works perfectly, it's great. I can't at all see why mine won't work the same way. Contains the same If statements / Background color dimensions. Bizarre

[sunny_talwar]

I guess paste a screenshot of your script (if it is not confidential)

[#7]

This script?

[sunny_talwar]

Can you add two new fields to the your last table (NewTable)

NewTable:
LOAD *,
          If([Actual Start Time] - [Scheduled Start Time] < (5/(24*60)) and [Actual End Time] -[Scheduled End Time] < (5/(24*60)), 1,
          If([Actual Start Time] - [Scheduled Start Time] > (5/(24*60)) and [Actual End Time] -[Scheduled End Time] > (5/(24*60)), 3, 2)) as ColorFlag,

Interval([Actual Start Time] - [Scheduled Start Time], 'hh:mm:ss') as Check1,

Interval([Actual End Time] -[Scheduled End Time], 'hh:mm:ss') as Check2
RESIDENT Actual_Data;

See what values are we getting for these fields for each of the 4 columns above?

[sunny_talwar]

This is what I am getting for Check1 and Check2

[#10]

Ahhh okay got it - I can see what the problem is (although not sure how to solve).

Within the Actual Data table there are multiple historical instances of the Runs, and so it is cumulatively adding these  together giving me huge numbers:

So I need a way of just applying this to today's date i.e. 25/06/2015

[sunny_talwar]

Is name unique in any of the two tables?