Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Date

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand

View solution in original post

6 Replies
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
Not applicable
Author

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-216654101-11-12 07:00
i-216670701-11-12 09:11
r-216709801-11-12 13:05
i-216717701-11-12 13:53
i-216737701-11-12 17:23
i-216775802-11-12 06:39
i-216853902-11-12 17:24
i-216906405-11-12 02:44
i-216908105-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

Gysbert_Wassenaar

See attached example


talk is cheap, supply exceeds demand
Not applicable
Author

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


Not applicable
Author

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

Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand