5 Replies Latest reply: Nov 29, 2012 5:45 AM by Andrea Rebora RSS

    Set analysis for date field

      Hi,

      I've check in the community, but, even if I found many discussions about this iusse, I can't figure it out.

      I have this expression:

      sum({$<[Stock.]={">0"}, Type = {S},    nDeliveryDate={<=$(VNumToday)}       >}[Value])

       

      But It returns a value as 0.

       

      What is wrong in  the part of  date set analysis ?

       

      Many thanks

       

      Andrea

        • Re: Set analysis for date field
          Stefan Wühl

          Try using doubles quotes with the nDeliveryDate set modifier.

           

          sum({$<[Stock.]={">0"}, Type = {S},    nDeliveryDate={"<=$(VNumToday)"}       >}[Value])

          • Re: Set analysis for date field
            Steve Dark

            Hi there,

             

            You need to be very careful with the format of your date field and the format of the date variable that you are comparing it to.  In Set Analysis 27/11/2012 does not equal 27 Nov 2012.

             

            Suggest forcing a format for the date field in your load script, eg:

             

            Date(nDeliveryDate, 'DD-MMM-YYYY') as nDeliveryDate,

             

            And then the same in your variable definition:

             

            =Date(today(), 'DD-MMM-YYYY');

             

            It can also be useful to use an Input Box to keep a close eye on what you actually have in your variables.

             

            Hope that helps,

             

            Steve

              • Re: Set analysis for date field

                Many thanks both Steve and sweuhl.

                I tried your suggestions, and now I have the value, unfortunately the set analysis does not work properly.

                In fact the value that results from the expression brings me out not only the value of the orders with the delivery date <= of today but even the value of the orders after today.

                See the attached file inside my question post

                  • Re: Set analysis for date field
                    Steve Dark

                    If you reference a fieldname in Set Analysis that doesn't exist in your data model this is simply ignored.  Could it be that nDeliveryDate appears slightly differently in the data model (note case sensitivity)?  Looking at the spreadsheet you uploaded, should the field read simply nDelivery?

                     

                    If you can upload a sample QlikView app it should be relatively quick to get to a solution.

                     

                    Regards,

                    Steve