3 Replies Latest reply: Mar 10, 2010 3:43 AM by Miguel Angel Baeyens de Arce RSS

    Date Compare using Set Analysis

      Hi,

       

      I am trying to compare dates (BTCreateDateTime) to the variable vBTCompareStartDate using the Set Anaylsis.

      The following works when I apply the change, however if I adjust the Variable using the calendar object the Chart does not react to it at all.

       

      Am I missing something?

       

      Sum({<BTCreateDateTime={"=Date(BTCreateDateTime, 'YYYY-MM-DD') >=Date(vBTCompareStartDate, 'YYYY-MM-DD')"}>} BTGross)

       

        • Date Compare using Set Analysis
          Miguel Angel Baeyens de Arce

          Hello,

          I'd try something like

          Sum({< BTCreateDateTime = {">=$(vBTCompareStartDate)"} >} BTGross)
          Should your variable and BTCreateDateTime field use different date formats, I'd rather set them similar in script, making easier further comparations.

            • Date Compare using Set Analysis

              Hi,

              I tried what you suggested and it just constantly returns the entire result set.

              I have now formated the date in the script how it is behaving the same.

              Does anyone have and decent examples of Sets return based on date ">=" and "<" - this what i need to do

                • Date Compare using Set Analysis
                  Miguel Angel Baeyens de Arce

                  Hello,

                  Above code is working perfectly for me and syntax is fine. If it's returning the entire set, it means it isn't matching the condition, and it's likely beacuse of the date format, should your variable (as I mentioned before) and your field have different date formats. If this is the case, you should format it as you were doing in your load script, instead of the expression.

                  Anyway, another sample code would be (working with fields and values)

                  Sum({< DateField = {">=$(=Date(varStartingDate))<=$(=Date(EndDateField))"} >} Amount)
                  where Date() function is used to format both variable and field in the same way.

                  Regards.