8 Replies Latest reply: Aug 11, 2011 2:58 PM by connextions1 RSS

    Set Analysis: Less Than Date

      Hi,

      I have a straight table chart which uses set analysis to derive distinct counts of invoices based on a independant date table with the fields Year & Month(which the user clicks on to filter the results). What I am trying to do is get a count of all invoices where the document date is LESS THAN the selected date.

      I have looked at quite a few other posts where the logic in the script should be as follows:

      ...
      [Document Date] = {'< $(=AddMonths(makedate(Year,Month,01),3))'},
      ...

      I am confused with the above logic, as my understanding is that "$(=" would convert to a number rather than remaining a date. So this would be equal to docdate:01/01/10 (gregorian) < UserSelectedDate:408752.8(julian).

      Regardless of my confusion(would be nice to understand), the above code does not work. Beneath is the code I have created which is intended to return invoices where the document dates are more than 3 months from the date I have selected

       

       

       

       

      =

      count(distinct

      {$<

      [Document Date] = {'< AddMonths(makedate(Year,Month,01),3)'},
      [Invoice Approved Year] = {$(#=year(AddMonths(makedate(Year,Month,01),0)))},
      [Invoice Approved Month] = {$(#=month(AddMonths(makedate(Year,Month,01),0)))

      }>}

      linkVendorInvoiceHeader)





      Year = Independant table user clicks on
      Month = Independant table user clicks on
      linkVendorInvoiceHeader = Invoice Key
      Invoice approved Year/Month = subset of records interested in.
      Document Date = 3 months prior to user selected date.

      I know the Document date line is the faulty element as I am using the remainder of the script a heck of a lot elsewhere..

      Please help...

      Kind regards,

      Jon.
      (losing sanity very very quickly over this)



        • Set Analysis: Less Than Date
          Neil Miller

          In Set Analysis, $(=) is a dollar sign expansion and simply evaluates the expression inside. It can return a number, date or string. You do have to watch for format issues though. AddMonths() could be returning a number (date as a number), which will cause problems in your Set Analysis. My first thought would be to add a Date() function around AddMonths().

          Try this for your expression:

          count(distinct
          {$<
          [Document Date] = {'< $(=Date(AddMonths(makedate(Year,Month,01),3)))'},
          [Invoice Approved Year] = {$(#=year(AddMonths(makedate(Year,Month,01),0)))},
          [Invoice Approved Month] = {$(#=month(AddMonths(makedate(Year,Month,01),0)))
          }>}
          linkVendorInvoiceHeader)


          When working with Set Analysis and dollar sign expansions, it is a good idea to put the expression into a chart, but don't give the expression a label. When the chart is rendered, the label will be your Set Analysis expression with the dollar sign expansions evaluated. That will show you what the dollar sign expansions are returning and you can compare that to your expression with hardcoded Element Sets.

            • Set Analysis: Less Than Date

              Hi,

              Thanks for the quick reply, I did see that date formats were a bit "iffy" and tried the date function with format of "dd/mm/yy" as well to no avil.

              The example you provided didnt work(minor change to -3 instead of 3 [on addmonth]). It is syntaxtically correct but evaulates to zero. When I know for definate there should be LOTS of invoices.

              I have put each element into a textbox to see if there are any other errors and everything appears to be in order. The dates are coming from SQL so they will all be in the same format & locale of qlikview...

              Any other thoughts?

              Thanks for your assistance.

                • Set Analysis: Less Than Date
                  Neil Miller

                  When you added in the date format, did you put quotes around the date format? You shouldn't have, the dollar sign expansion already has quotes, so putting another set of quotes inside would break that. It should look something like:

                  count(distinct
                  {$<
                  [Document Date] = {'< $(=Date(AddMonths(makedate(Year,Month,01),-3), MM/DD/YYYY))'},
                  [Invoice Approved Year] = {$(#=year(AddMonths(makedate(Year,Month,01),0)))},
                  [Invoice Approved Month] = {$(#=month(AddMonths(makedate(Year,Month,01),0)))
                  }>}
                  linkVendorInvoiceHeader)


                  Did you put the expression into a table and look at the Set Expression? Could you paste that in here (right-click, Copy to Clipboard, Cell Value). You should be seeing something like"

                  count(distinct
                  {$<
                  [Document Date] = {'< 7/4/2011'},
                  [Invoice Approved Year] = {2011},
                  [Invoice Approved Month] = {7
                  }>}
                  linkVendorInvoiceHeader)


                  Obviously, my numbers would be different, but you should have quotes around the full date and the other two should be numbers.

                    • Set Analysis: Less Than Date

                      Hmmm,

                      Okay may be getting somewhere (albiet backwards). I copy and pasted the hardcoded version as a new expression and still got zeros.

                      I have also clicked on each value to filter results to the same as what the set analysis should be doing and there are LOTs of recs on the transaction table I have on another tab in Qlikview.

                      I think there is something wrong with the document date field (i.e how it is being evaluated). But it is a date in SQL (just check SQL schema), also looked at the number tab in doc properties and it is set as a timestamp (switched to date but still didn't work).

                      arghhh! This is starting to sound like a support call to me :(

                      Jon

                        • Set Analysis: Less Than Date
                          Neil Miller

                          Did you get rid of the space as Roland suggested?

                          If the hardcoded version is returning zeros (not nulls), then the syntax is okay, but the logic returns no values. What does your date field look like when it is in a ListBox? Even if it is loaded from SQL as a date, you need to verify the format. Set Analysis and dates are very dependent on format. You may think that the field is a date, but if the format is different, QlikView will treat it differently.

                          When working with Set Analysis, it is a good idea to first get your expression working with hardcoded values. Once it is working with hardcoded values, then move on to the dollar sign expansions.

                            • Set Analysis: Less Than Date

                              Hi,

                              For completeness here is some further info as to how I am using it now. Although some success was made with Roland & NMillers suggestions (thanks guys!). I discovered that the date needed to be defined as a "date" instead of "timestamp" within the "Numbers" tab in document properties.

                              Also, the following revised script worked very well...

                               

                               

                              [Invoice Approved Date]

                               

                               

                              ={">=$(=addmonths(monthstart(max(Date)),-11))<=$(=addmonths(monthend(max(Date)),0))"

                              }

                              Hope this helps other people with the same issue..





                                • Re: Set Analysis: Less Than Date

                                  For the sake of other people who may have struggled with this, as I did:

                                   

                                  Chart expressions, outside of set analysis, seem to be fine with comparing dates and timestamps.  In set analysis, on the other hand, I had to specifically format my date as a timestamp for it to be compared to a date.

                                   

                                   

                                  =count(
                                      DISTINCT
                                      {$<[Customer Create Date] = {'<=$(=Timestamp(PointInTimeForAnalysis))'}>}
                                      [Customer ID]
                                  )
                                  
                          • AW:Re: Set Analysis: Less Than Date

                            Hi there,

                            in addition to the very good explanation of nmiller and as a suggestion for your second question:

                            I also thought of using the Date()-Function and I found out that it is not necessary. But a very lousy pit trap is having a blank between the "<" and the "$(=". Take a look at the following simplified code, which is working fine:

                            =count( { $ < FromDate = { "<$(= AddMonths(makedate(2010,10,01),3))" } >} Value)


                            As you can see, there must be NO blank in "<$(=

                            BTW: I prefer the double quotes to show QV this is a search mask. Of course It is working fine with single quotes which is to say this is a literal.

                            Regards, Roland