set analysis today()

[#1]

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

[mike_garcia · Accepted solution]

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.

[#3]

Thanks for your help Mike!

It works perfectly.

Best Regards,

Jonas

[#4]

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

[#5]

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

[mike_garcia]

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.

[mike_garcia]

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.