Date Time filter

[Anonymous]

Hi

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')

AS

START_DATE,

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

AS

END_DATE,

[Anonymous]

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....

[johnw]

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)

[Anonymous]

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 ?

[johnw]

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.

[Anonymous]

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 ...

[johnw]

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.

[Anonymous]

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')

AS

FINISH_DATE,

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

AS

START_DATE,

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

AS

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)"}

[johnw]

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.

[Anonymous]

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...