Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

set analysis today()

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

Tags (2)
1 Solution

Accepted Solutions
mike_garcia
Contributor III

set analysis today()

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.

6 Replies
mike_garcia
Contributor III

set analysis today()

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.

Not applicable

set analysis today()

Thanks for your help Mike!

It works perfectly.

Best Regards,

Jonas

Not applicable

set analysis today()

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

Not applicable

set analysis today()

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
Contributor III

set analysis today()

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
Contributor III

set analysis today()

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.

Community Browser