Conditional formatting on time fields

[#1]

I am fairly new to Qlik but having problems getting my head around conditional formatting on time

I have named objects that go through 5 stages and each stage is time stamped, i have a table that shows the time that each of the stage is completed and a scheduled completion time field and what I would like to do is set up the formatting that for example is

If Stage A  is <= Scheduled time - 2hrs then green

If Stage A is <=Scheduled time - 1 hr then Amber

If Stage A >= Scheduled Time then Red

Can manage this for actual numbers but cant seem to get it to work as these are time fields HH:MM

[its_anandrjs]

Try with this by click on the + sign in expression

If( Stage <=  Time#(Scheduled time,'hh' ) - 2, Green( ),

If( Stage <=  Time#(Scheduled time,'hh' ) - 1, Blue(),

If( Stage >=  Time#(Scheduled time,'hh' ),Red())))

[#3]

Have tried this and it does not appear to be working cant see any colour changes in my chart

[jsingh71]

Plz post any sample application then you will get correct answer in less time.

[#5]

How do I attached a sample qvd file, can see an option to insert a video or an image but not anything else,

[PradeepReddy]

While replying, If u did n't find the attach option in i nthe editor, switch to "Advance Editor" option. there you can find the file atatch option.

[Anonymous · Accepted solution]

Hello, Sue.

Dates and Times in QlikView are stored as a number where the integer part correspond to the day since 30th December 1899 and the decimal part to the fraction of a day. For example, today's date (29th May 2014) is 41788, and today at 8:35 am is 41788.357638889. (Actualy, they are stored in a dual format, with a string representation besides the number, but we are concerned with the number part.)

So if we subtract two timestamps with the same day but different times, we'll get a fractional number like 0.083333. The trick would be to know which number corresponds to intervals of one hour and two hours. But it's very easy. Since we know one day is the integer 1, one hour is 1/24 and two hours is 2/24. If w'd want minutes, we'd divide further for 60 (1/24/60), and so on.

Now we can code our conditional format:

if([Scheduled time] - [Stage A] >= 2/24, RGB(0, 255, 0),

if([Scheduled time] - [Stage A] >= 1/24, RGB(255, 126, 0),

if([Scheduled time] <= [Stage A], RGB(255, 0, 0))))

Notice we are missing the condition when 0h < ([Scheduled time] - [Stage A]) < 1h.

I hole this helps.

Bruno.