topic set analysis today() in QlikView

set analysis today()

Mon, 28 Jun 2010 15:45:26 GMT

Hi,

I'm new to Set Analysis and have trouble getting it to work with dates. I want to coutn the number of Purchase Orders that is Past Due, i.e number of "PO Action" that have a Promise Date earlier then today.

=count( {1<[PO Promise Date]={today()}>} [PO Action])

How do I write the Today() statement?

Any help would be appreciated!

Best Regards,
Jonas

set analysis today()

mike_garcia — Mon, 28 Jun 2010 15:52:24 GMT

Hello,

When using Set Analysis with Dates, you must be careful that the format of the date field matches what you specify in your Set Expression.

Assuming your Format is MM/DD/YYYY, try this:

=Count( {1<[PO Promise Date] = {$(=Date(Today(), 'MM/DD/YYYY'))}>} [PO Action])

That will give you a count of PO Action where the PO Promise Date EQUALS Today(). If you want to get those dates EQUAL or BEFORE Today(), then use this:

=Count( {1<[PO Promise Date] = {"<=$(=Date(Today(), 'MM/DD/YYYY'))"}>} [PO Action])

Hope this helps.

Mike.

set analysis today()

Tue, 29 Jun 2010 12:08:46 GMT

Thanks for your help Mike!

It works perfectly.

Best Regards,

Jonas

set analysis today()

Sat, 26 Mar 2011 21:36:08 GMT

Help - Could someone PLEASE tell me why this does not work:

=Count( {1<[Submit Date-T] = {$(=Date(Today(), 'YYYY-MM-DD') ) }>} [Incident Number]

It comes back with nothing- not 0 - it acts like there is some type of code error - but no error shows when editing it.

This works fine

=Count( {1<[Submit Date-T] = {'2011-03-26'}>} [Incident Number])

Thank you in advance - I have tried all type of syntax to no avail

Jim

set analysis today()

Sat, 26 Mar 2011 21:46:19 GMT

Sorry htis is what is not working somehow missed the last bracket when pasting

=Count( {1<[Submit Date-T] = {$(=Date(Today(), 'YYYY-MM-DD') ) }>} [Incident Number])

Jim

set analysis today()

mike_garcia — Sun, 27 Mar 2011 17:56:46 GMT

Hello!

If you evaluate the above expression to see what QlikView is actually interpreting, you will get:

=Count( {1<[Submit Date-T] = {2011-03-27}>} [Incident Number])

As you can see, it is not the same as the one you said is working, you are missing the single quotes. So, just add them to enclose the dollar-sign expansion. You should have something linke:

=Count( {1<[Submit Date-T] = { '$(=Date(Today(), 'YYYY-MM-DD'))' }>} [Incident Number])

* Try to remove unnecessary spaces between the single quotes.

* You can also use double quotes in this case.

Regards,

Miguel.

set analysis today()

mike_garcia — Sun, 27 Mar 2011 17:59:36 GMT

I just noticed, the date expression is using single quotes also, so if you add other pair of single quotes it will cause some trouble. Use double quotes instead.

=Count( {1<[Submit Date-T] = { "$(=Date(Today(), 'YYYY-MM-DD'))" }>} [Incident Number])

Miguel.