topic Re: Current Date variable in set analysis not working in QlikView

Current Date variable in set analysis not working

Tue, 16 Dec 2014 12:04:49 GMT

Hello

I'm having trouble getting correct answer when using a variable I created (to give the CurrentDate value) in the set analysis part of an expression I am creating. I'd like to use this in various objects in a dashboard the following example is from a simple text object I've created just to make sure the answer is correct!

What I'm trying to show is a list of Work Orders which:

a) have a status of "Open" and

b) are assigned to an engineer on a date in the future

Ultimately, when I'm happy that I'm getting the correct answer, the expression will be used in a straight table to list the required Work Orders. For example what I'm asking is, display list of work orders based on: count of work orders ("wr_id" field) where status = "open" and date_assigned >=CurrentDate (greater or equal to today's date)

Using the actual date typed into the set analysis of the expression gives the correct answer (which is 17):

=count(DISTINCT{<status_cat={'open'}, date_assigned={'>=16/12/2014'}>}wr_id)

BUT if I try using the CurrentDate variable I set up (vCurrentDate), it gives an answer of "0"

=count(DISTINCT{<status_cat={'open'},date_assigned={">=$(vCurrentDate)"}>}wr_id)

=count(DISTINCT{<status_cat={'open'},date_assigned={'>=$(vCurrentDate)'}>}wr_id)

As another example, if I try this I get "-" as the result in the text object:

=count(DISTINCT{<status_cat={'open'},date_assigned={>=$(vCurrentDate)}>}wr_id)

I've tried all of the examples above and neither give the correct answer!

The variable is OK as I've tested this in a text object to make sure it does return the date and it's OK.

the variable is created in the script as: set vCurrentDate = today();

BUT for some reason it doesn't seem to like it when I use it in the expression as the set analysis part of the date_assigned field (even though the Edit Expression dialogue box indicates the expressions are OK).

I've searched the forums can't find anything which helps so far. If anyone can tell me where I'm going wrong, I would greatly appreciate it!

Thanks

Dan

Re: Current Date variable in set analysis not working

MK_QSL — Tue, 16 Dec 2014 12:07:58 GMT

What is the default date format of your script?

CHECK

SET DateFormat='DD/MM/YYYY';

Or use

LET vCurrentDate = today();

Re: Current Date variable in set analysis not working

jpenuliar — Tue, 16 Dec 2014 12:10:21 GMT

try ">=$(=date($(vCurrentDate)))"

Re: Current Date variable in set analysis not working

Tue, 16 Dec 2014 13:35:30 GMT

Hi Manish

default date format is set how you've described

added new variable based on LET type and it worked with following:

=count(DISTINCT{<status_cat={'open'},date_assigned={'>=$(vCurrentDate3)'}>}wr_id)

now need to read up on difference between SET and LET variables.....

Thanks for pointing me in right direction

Dan

Re: Current Date variable in set analysis not working

Tue, 16 Dec 2014 13:38:07 GMT

Hi Jonathan

I tried the other suggestion first, which worked so marked that one as correct answer, but then tried your suggestion as well, which also worked with following:

=count(DISTINCT{<status_cat={'open'},date_assigned={">=$(=date($(vCurrentDate)))"}>}wr_id)

So thanks you for your help in pointing me in right direction

Dan