6 Replies Latest reply: Mar 27, 2011 1:59 PM by Miguel �ngel Garc�a RSS

    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

        • set analysis today()
          Miguel �ngel Garc�a

          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()

              Thanks for your help Mike!

              It works perfectly.

               

              Best Regards,

              Jonas

                • set analysis today()
                  jim OConnor

                  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()
                      jim OConnor

                      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()
                          Miguel �ngel Garc�a

                          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.