topic Re: Date in QlikView

Date

Sat, 22 Dec 2012 04:48:55 GMT

Hi ,

I have 3 columns Order number, Status, Date( which contains the date and time of the order) . Now in a staright table or pivot table i will use these columns but the background colour of the order number must be red if the difference between(in hours) Date and present date exceeds 48 hours whose status is 'Not delivered'.

Example :My data looks like this

Order number, Status, Date

12, delivered, 2012-12-1 12:30:48

10, not delivered, 2012-12-22 10:02:21

15, not delivered, 2012-12-19 23:21:10

. In this case for the orders 10 and 12 the background colour must be white(for the column order) since they are not exceeded 48 hours. Where as for order 15 it should display with red colour.

In this there are 2 senarios . What if the time format is 24 hours and what if the format is 12 hours(am and pm).

Re: Date

Gysbert_Wassenaar — Sat, 22 Dec 2012 10:21:40 GMT

This expression should do it if your Date field is already a timestamp. The formatting of the time doesn't matter.

if(Status='not delivered' and Now()-Date>2, red(),white())

But if your Date field is a string you need to make a timestamp of it first:

for a 24 hour format: timestamp#(Date,'YYYY-MM-DD hh:mm:ss')

for an am/pm format: timestamp#(Date,'YYYY-MM-DD hh:mm:ss tt')

I've used the now() function which includes the time. If you want the start of today instead use the today() function.

Now()-Date>2 means the difference between now() and Date is larger than 2 days, i.e. 48 hours.

Re: Date

Sat, 22 Dec 2012 15:55:54 GMT

Hi Gysbert,

I tried in different ways . But it is not giving what i require . More over Now()-Date nor TOday()-Date is not giving the correct output. my data looks like this

Ticket No.	Date Opened
i-2166541	01-11-12 07:00
i-2166707	01-11-12 09:11
r-2167098	01-11-12 13:05
i-2167177	01-11-12 13:53
i-2167377	01-11-12 17:23
i-2167758	02-11-12 06:39
i-2168539	02-11-12 17:24
i-2169064	05-11-12 02:44
i-2169081	05-11-12 03:15

Can you please create a small application with this data and can you give me what i require

and correct me where i am going wrong.

In the application please use both NOW and Today functions for clear understanding how they work

Re: Date

Gysbert_Wassenaar — Sat, 22 Dec 2012 17:23:47 GMT

See attached example

Re: Date

Sun, 23 Dec 2012 06:46:30 GMT

Hi Gysbert,

Thankq but

when i am trying to use this expression "if(Status='not delivered' and Today()-Date>2, red(),white())", it showing red colour to all those status which are nor delivered please assist. it is not taking today()- Date>2. Please assist

Re: Date

Sun, 23 Dec 2012 07:18:37 GMT

Hey ,

now it is working . But there is a second condition where it has to satisfy

It has to show red if

status is not delivered and

State is not equals to 'closed or resolved'

Today()-Date>2

It has to satisfy all three conditions then only it should display in red colour please suggest

Re: Date

Gysbert_Wassenaar — Sun, 23 Dec 2012 10:29:06 GMT

Try:

if(Status<>'delivered' and State<>'closed or resolved' and Today()-Date>2, red(), white())

Or if closed and resolved are separate states try:

if(Status<>'delivered' and State<>'closed' and State<>'resolved' and Today()-Date>2, red(), white())