Date Comparison

[#1]

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!

[#2]

you can do a

LOAD

*

Resident

TABLENAME

Where

DATE_ADDED > $(vMinu180)

hope this helps.

[jagan]

HI,

Try this

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

Hope this helps you.

Regards,

Jagan.

[#4]

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

[jagan]

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.

[#6]

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

[tresesco · Accepted solution]

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.