Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
We value your feedback – take our 5-minute QlikView modernization survey
cancel
Showing results for 
Search instead for 
Did you mean: 
balrajprabhu
Creator II
Creator II

If max(salesDate) is less than now() by 32 hours then turn red else green

Hi,

I have a field sales date, I want to turn it red if its older than 32 hours  from now i.e now() function which picks the date and time from the system.

I tried some like this

if ( max(sales date) < today() , red() )

How do I add 8 hours to today() ?

the format of sales date is as follows

2015-05-17 23:59:02.000

1 Solution

Accepted Solutions
mov
Employee
Employee

Correct, you have to subtract lesser date from  a greater date, and the current date is always greater.  A few notes:

1. I would not recommend using now() function in the front end expressions, they could be CPU-consuming (depending on QV version, but better to avoid altogether).  Use reloadtime() instead if you reload multiple times a day, or today() if you don't reload every day.

2. There is no need to use num() here.  Timestamps are dual, that is they have both text and numeric value.

3. Not sure why you need max().  It may depend on chart of course...

So, you get:

if(reloadtime()-saleDate > (1+3/8), red(),green())      // 3/8 is 9/24, for 33 hours (24+9)

View solution in original post

9 Replies
ramoncova06
Specialist III
Specialist III

.33 is equal to 8 hrs so just add a +.33 to you expresion


if ( max(sales date) < (today()+.33) , red() )

mov
Employee
Employee

There is some disconnect between the title of the discussion, its content, and expression logic.  So, my assumption is to use red if sales is older than 32 hours.  I'd rather use reloadtime() than today():

if((salesdate - reloadtime()) > (1+1/3), red())

balrajprabhu
Creator II
Creator II
Author

Hi Mike,

you are right,I wasn't very clear.

The requirement is some thing like this

max(salesdate) is 33 hours older than reload time then ,red()


mov
Employee
Employee

32 hours or 33?

If 33, it is

(1+9/24) or (1+3/8)

balrajprabhu
Creator II
Creator II
Author

Hey Mike,

I was wondering shouldn't  the formula be something like

 

if (num(now())-max(saleDate) > (1+1/3),red(),green()) 

Because we are subtracting  older date i.e max(saleDate)  from now(), as now() is always greater than max(saleDate) ?

Not applicable

use reloadtime()

reloadtime() than today():

if((salesdate - reloadtime()) > (1+1/3), red())

balrajprabhu
Creator II
Creator II
Author

Thanks for the reply,

I can't use reladd time, because sometimes the Datawarehouse may not get reloaded with upto date data.So Requirment is to use the max(salesDate) .Here Max(salesDate) is used to determine the freshness of data

mov
Employee
Employee

Correct, you have to subtract lesser date from  a greater date, and the current date is always greater.  A few notes:

1. I would not recommend using now() function in the front end expressions, they could be CPU-consuming (depending on QV version, but better to avoid altogether).  Use reloadtime() instead if you reload multiple times a day, or today() if you don't reload every day.

2. There is no need to use num() here.  Timestamps are dual, that is they have both text and numeric value.

3. Not sure why you need max().  It may depend on chart of course...

So, you get:

if(reloadtime()-saleDate > (1+3/8), red(),green())      // 3/8 is 9/24, for 33 hours (24+9)

swarup_malli
Specialist
Specialist

try this

if(interval(now()-max(salesdate) > (1+1/3),red())