topic Re: Date Comparison in QlikView

Date Comparison

Thu, 18 Jul 2013 17:25:21 GMT

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

Thu, 18 Jul 2013 17:30:24 GMT

you can do a

LOAD

Resident

TABLENAME

Where

DATE_ADDED > $(vMinu180)

hope this helps.

Re: Date Comparison

jagan — Thu, 18 Jul 2013 17:31:04 GMT

HI,

Try this

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

Hope this helps you.

Regards,

Jagan.

Re: Date Comparison

Thu, 18 Jul 2013 18:30:54 GMT

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 — Thu, 18 Jul 2013 18:55:33 GMT

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

Thu, 18 Jul 2013 20:34:15 GMT

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

tresB — Fri, 19 Jul 2013 08:01:49 GMT

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.