6 Replies Latest reply: Jul 19, 2013 4:01 AM by Tresesco B RSS

    Date Comparison

      I would like to get all records that have been added in the last 180 days. I'm bringing my DateAdded field in via a SQL Select statement. It is a datetime field.

       

      I'm setting the following variables because I want today to actually be 12/31/2012. Then I'm creating a variable that gets a date that is 180 days in the past as a datetime format.

       

      Let vStartDate = makedate(2012,12,31);

      Let vMinus180 = date(vStartDate-180, 'M/D/YYYY hh:mm:ss TT');

       

      I basically want an expression that shows the count of orders that have a date from the vStartDate to the vMinus180. Something like:

       

      =vStartDate - DateAdded <= 180

       

      or something else using the vMinus180 variable.

       

      Thanks in advance for any help!

        • Re: Date Comparison

          you can do a

          LOAD

          *

          Resident

          TABLENAME

          Where

          DATE_ADDED > $(vMinu180)

           

          hope this helps.

          • Re: Date Comparison
            jagan mohan rao appala

            HI,

             

            Try this

             

             

            =Count(Date={'>=$(=vStartDate)<=$(=vMinus180)'}, YearDimensionName=, MonthDimensionName=, QuarterDimensionName=, WeekDimensionName=>} RecordID)

             

            Hope this helps you.

             

            Regards,

            Jagan.

              • Re: Date Comparison

                What are you doing with the dimensions? What are they for? I just want a count of records where DateAdded > vMinus180. Not sure I follow what you are doing with the dimensions.

                 

                Thanks,

                 

                Justin

                  • Re: Date Comparison
                    jagan mohan rao appala

                    Hi,

                     

                    =Count(Date={'>=$(=vStartDate)<=$(=vMinus180)'}, YearDimensionName=, MonthDimensionName=, QuarterDimensionName=, WeekDimensionName=>} RecordID)

                     

                    Date={'>=$(=vStartDate)<=$(=vMinus180)'} - This restricts the date to last 6 months

                    YearDimensionName - Excludes the year dimension

                    MonthDimensionName - Excludes the month dimension

                    QuarterDimensionName - Excludes the quarter dimension

                    WeekDimensionName- Excludes the week dimension

                     

                    If any month is selected then only that month value is displayed in graph, that is why we are excluding the selection.

                     

                    Regards,

                    Jagan.

                      • Re: Date Comparison

                        Jagan,

                         

                        Date={'>=$(=vStartDate)<=$(=vMinus180)'}  Is this assuming I have a Date dimension?

                         

                        I actually don't have a date dimension. There also seems to be an error because there are two closing curly brakets and only one open.

                         

                        Thanks,

                         

                        Justin

                          • Re: Date Comparison
                            Tresesco B

                            try :

                             

                            =Count({<AddedDate={'>=$(=vStartDate)<=$(=vMinus180)' }>} AddedDate)

                             

                             

                            vStartDate = makedate(2012,12,31);

                            vMinus180 = date(vStartDate-180, 'M/D/YYYY hh:mm:ss TT');

                            vMinus180 = date(vStartDate-180);   // to ensure that both the variables are in default date format.