Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Highlighted
balrajprabhu
Contributor 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
Esteemed Contributor III

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

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
Valued Contributor III

Re: If Date of sale is less than 32 hours then red

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


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

mov
Esteemed Contributor III

Re: If Date of sale is less than 32 hours then red

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
Contributor II

Re: If Date of sale is less than 32 hours then red

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
Esteemed Contributor III

Re: If max(salesDate) is less than reload() by 33 hours then red ()

32 hours or 33?

If 33, it is

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

balrajprabhu
Contributor II

Re: If max(salesDate) is less than reload() by 33 hours then red ()

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

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

use reloadtime()

reloadtime() than today():

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

balrajprabhu
Contributor II

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

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
Esteemed Contributor III

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

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

swarup_malli
Valued Contributor

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

try this

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