topic Conditional formatting on time fields in QlikView

Conditional formatting on time fields

Thu, 29 May 2014 10:55:34 GMT

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

Re: Conditional formatting on time fields

its_anandrjs — Thu, 29 May 2014 11:01:20 GMT

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

Re: Conditional formatting on time fields

Thu, 29 May 2014 11:34:10 GMT

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

Re: Conditional formatting on time fields

jsingh71 — Thu, 29 May 2014 11:35:49 GMT

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

Re: Re: Conditional formatting on time fields

Thu, 29 May 2014 11:51:57 GMT

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

Re: Conditional formatting on time fields

PradeepReddy — Thu, 29 May 2014 12:01:13 GMT

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.

Re: Conditional formatting on time fields

Anonymous — Thu, 29 May 2014 12:01:27 GMT

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.