6 Replies Latest reply: Mar 27, 2011 1:59 PM by Mike Garciam 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()
          Mike Garciam

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

                          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 Garciam

                              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.