topic Re: Date comparison in QlikView

Date comparison

Tue, 13 Aug 2013 08:26:28 GMT

Hi,

I need to compare whether the end date is within next 5 days from the current date ,where end date is a column having date entries.Incase the end date is within forth coming 5 days i would want to display 'Y',else 'N'.Can anyone let me know how this could be accomplished?

Re: Date comparison

swuehl — Tue, 13 Aug 2013 08:33:15 GMT

I would do this in the script:

Set DateFormat = 'MM/DD/YYYY'; //Whatever your input date format is

LOAD

EndDate,

if(EndDate -Today(1) <=5 and EndDate -Today(1) >0, 'Y','N') as EndDateFlag,

....

FROM .... ;

Re: Date comparison

Tue, 13 Aug 2013 08:43:15 GMT

But,I do not want to make any changes to the script I want it to be done in the front end

Re: Date comparison

swuehl — Tue, 13 Aug 2013 08:49:33 GMT

You can use the same logic in a table chart expression, when your dimension granularity is your end date:

=if(EndDate -Today(1) <=5 and EndDate -Today(1) >0, 'Y','N')

Re: Date comparison

Tue, 13 Aug 2013 08:50:08 GMT

in front end--

then prperties of pivot/straight-->number tab--> select radio button of Date --> then write format of date which you want

Re: Date comparison

Tue, 13 Aug 2013 08:59:03 GMT

Hi Swuehl,

I am new to qlikview and thus I am not familiar with today() function .Can you please explain when and where is it used. So that I am aware of the results that would be expected (I specifically want to understand the result of today(1))

Re: Date comparison

swuehl — Tue, 13 Aug 2013 09:03:03 GMT

From the HELP file (e.g. by pressing F1):

today([timer_mode] )

Returns the current date from the system clock. The timer_mode may have the following values:

0 Date at script run
1 Date at function call
2 Date when the document was opened

Default timer_mode is 2. The timer_mode = 1 should be used with caution, since it polls the operating system every second and hence could slow down the system.

So using today(1) in the front end is not a good idea, I should have changed the timer mode to e.g. 2 when copying the expression from the script.