topic Date Time filter in QlikView

Date Time filter

Anonymous — Tue, 06 Jul 2010 20:03:14 GMT

I have a datetime field finish_time , which i converted to date fields as below, i want to get the data where the finish_time is between start_date and end_date, i have the calender bos set up for start_date and end_date in my report and user has to select two different dates from them , but when i select a date from start_date the same reflects in end_date , how do i over come this?

Basically i am trying to get the job data which are finished between selected dates by user , Please Help

TO_CHAR(FINISH_TIME,'MM/DD/YYYY')

START_DATE,

TO_CHAR(FINISH_TIME,'MM/DD/YYYY')

END_DATE,

Date Time filter

Anonymous — Tue, 06 Jul 2010 21:48:16 GMT

Please Let me know if the question is not clear , basically i am looking for an option where i can set finish_time between the select dates....

Date Time filter

johnw — Tue, 06 Jul 2010 23:40:14 GMT

Simple solution is to remove your START_DATE and END_DATE fields, and just have a FINISH_DATE field. If the user wants to see data between two dates, they can select the date range between those two dates. A multi-value slider bar would make this fairly easy. So would having year and month fields in many cases. Lots of ways to make it easy to select a range.

If you really want to stick with a START_DATE and END_DATE, you'll still probably want a FINISH_DATE field. You could make START_DATE and END_DATE be variables instead of fields. User enters the two dates, and you calculate something like this:

count({<FINISH_DATE={">=$(START_DATE) <=$(END_DATE)"}>} distinct JobID)

Date Time filter

Anonymous — Wed, 07 Jul 2010 00:39:38 GMT

How do i select date range , from multi value slider bar...

i have selected calender/slider and gave field expression as finish_date and selected option multi ,

is that the right way to do ?

Date Time filter

johnw — Wed, 07 Jul 2010 00:48:27 GMT

Yes, that's the right way to do it. If you click on the bar, it will just give you a single date. If you hover over that one date, you'll have a hand cursor, and you can move the one date. If you move a little to the side, you should get a side to side arrow cursor. You can then expand into a date range. As before, the hand will move it (this time the whole range), and moving a little to the side of either end will let you change the size of the range.

It may not be what you need for this application, but it's at least an alternative to be aware of.

Date Time filter

Anonymous — Wed, 07 Jul 2010 01:05:26 GMT

Ok Got it ! Thank you , So can i select a date range using a calender style also , i think that would be more user frindly , selecting date range in calender ...

Date Time filter

johnw — Wed, 07 Jul 2010 17:00:31 GMT

I agree that it would be more user friendly in many cases if you could select a date range from a calendar object in some way. Unfortunately, I don't believe that's directly supported. Thus my other suggestion to use two variables, which you can set with two calendars.

"If you really want to stick with a START_DATE and END_DATE, you'll still probably want a FINISH_DATE field. You could make START_DATE and END_DATE be variables instead of fields. User enters the two dates, and you calculate something like this:

count({<FINISH_DATE={">=$(START_DATE) <=$(END_DATE)"}>} distinct JobID)"

I'm sure there are other approaches as well if that one isn't good for you either.

Date Time filter

Anonymous — Wed, 07 Jul 2010 17:31:15 GMT

I get some syntax error 😞

here is my code , i want the finish_date between start_date and end_date filter selected values ,

sql

select

TO_CHAR(FINISH_TIME,'MM/DD/YYYY')

FINISH_DATE,

TO_CHAR(FINISH_TIME,'MM/DD/YYYY')

START_DATE,

TO_CHAR(FINISH_TIME,'MM/DD/YYYY')

END_DATE,

TO_CHAR(FINISH_TIME,

'DY')AS

DAY,

"JOB_ID",

"USER_NAME",

"QUEUE_TIME",

"START_TIME",

"FINISH_TIME",

"PROJECT_NAME",

"QUEUE_NAME",

"CLUSTER_NAME",

"MEM_REQUEST",

"MEM_USAGE",

"SWAP_USAGE",

"CPU_MINUTES",

"JOB_EXIT_STATUS",

"JOB_EXIT_CODE",

"JOB_NAME",

"JOB_ARRAY_INDEX",

"JOBRES_RAW",

"JOB_CMD",

"EXECHOST_NAME",

"RUN_MINUTES",

"PENDING_MINUTES",

"JOB_TYPE"

from

PIOWNER."DW_T_LSF_JOBMART" where "CLUSTER_NAME" IN

('STG','LAB')

AND

FINISH_DATE

={">=$(START_DATE) <=$(END_DATE)"}

Date Time filter

johnw — Wed, 07 Jul 2010 18:44:17 GMT

Heh. Not what I meant at all. Let me just post an example. I think it will be more clear than trying to explain it.

Date Time filter

Anonymous — Wed, 07 Jul 2010 20:04:06 GMT

Thank you very much for the reply , how did you create the variable To and From ?

Sorry for my basic question , i am new to qlikview...

Date Time filter

johnw — Wed, 07 Jul 2010 20:12:03 GMT

There are several ways to do it. Here's the one I used:

Settings -> Document Properties -> Variables -> New

Date Time filter

Thu, 08 Jul 2010 13:20:57 GMT

Dear John when i copy your script in my qvw file and make two variable From and to

then again i copy your expression in my my chat it give me no value .

I attached my qvw file here with kindly check it.

Regards

Ashish Srivastava

Date Time filter

Fri, 09 Jul 2010 05:07:27 GMT

Please help me this issue.

Regards

Ashish Srivastava

Date Time filter

Fri, 09 Jul 2010 07:50:48 GMT

please please------------------------------

help me urgent basic.

Regards

Ashish

Date Time filter

Anonymous — Fri, 09 Jul 2010 18:17:55 GMT

Ashish,

Create a new post and explain what you are trying to achive and what is the error your are getting in a new post ,

Then people might help you with what you want....

Date Time filter

johnw — Sat, 10 Jul 2010 00:14:21 GMT

You copied only what you thought was the relevant portion of the script, and missed a very important piece. Your date format doesn't match the date format being read in. You're using 'M/D/YYYY', though not consistently. The dates are in 'D-M-YYYY'. Your date formats all need to be consistent. You can either fix your date formats to all match the inline load, or you can fix the inline load and your date formats to all be 'M/D/YYYY'. Either will fix your problem.

Date Time filter

wizardo — Sat, 10 Jul 2010 09:32:18 GMT

hi, you can select a range of dates in a calendar style too, if you use the CTRL button while you select days you can select all the days of one month and then move to the next month and continue the selection (if you keep the CTRL button pressed)