topic Re: Filter for Previous Day in QlikView

Filter for Previous Day

elduderino — Wed, 26 Feb 2020 12:28:06 GMT

Hello Everyone,

I have a straight table chart that needs to show only records for the previous day (yesterday). This table has 8 dimensions and no expressions, as no calculations are needed. The dimension I am trying to filter on is a date/time field (scheduled_dt_tm), and everything I have tried has not worked.

Is a calculated dimension the route I should take? Here is what I have tried to no avail:

=if(SCHEDULED_DT_TM = (today()-1), SCHEDULED_DT_TM)

Am I on the right track? Any help would be greatly appreciated, thanks!

Re: Filter for Previous Day

tresB — Wed, 26 Feb 2020 12:35:03 GMT

If your timestamp has time fraction as well, you should try comparing integers instead, like

if( Floor(SCHEDULED_DT_TM)= (today()-1), SCHEDULED_DT_TM)

Re: Filter for Previous Day

Gabriel — Wed, 26 Feb 2020 12:45:18 GMT

Hi,

I will suggest to Flag all prev day in script and simplify your expression in UI.

And depending on SCHEDULED_DT_TM date format, you might have to format the date using DATE# and Date. I have used FLOOR() function to stripe out timestamp

Example:

NOCONCATENATE

tmp1:
LOAD *
, IF(FLOOR(SCHEDULED_DT_TM )=DATE(TODAY()-1,'DD/MM/YYYY'),1,0) AS [%Prev Day]
RESIDENT tmp;

DROP TABLE tmp;

So you expression can be

Dim: Dim1

Exp: SUM({<[%Prev Day] ={1}>} Sales)// this is an example, you can expand you SA as you wish.

Please let me know if this helps