topic Re: Date Range and time range is not working in QlikView

Date Range and time range is not working

Wed, 25 Nov 2020 16:16:04 GMT

I have scenario.

Need to select date range and time range using filters.For that i have created two calendar object with variables.

In addition to this i need to add time filters also like the attached image.

Requirement is count of orders based on selections like date range i.e. satrtdate and enddate

and time range i.e. 8am to 10 am like that.

Please do the needful

Regards

Thiru

Re: Date Range and time range is not working

sunny_talwar — Mon, 13 Mar 2017 08:52:27 GMT

May be try this:

Count({<YearMonth={">=$(=Date(Vstartdate, 'YYYY/MM/DD h:mm:ss TT'))<=$(=Date(Venddate, 'YYYY/MM/DD h:mm:ss TT'))"}, Time = {"=Frac(Time) >= MakeTime(8) and Frac(Time) <= MakeTime(10)"}>} Country)

Re: Date Range and time range is not working

Mon, 13 Mar 2017 10:08:17 GMT

Hi Sunny,

In addition to the above how to add time filters?

If i take time column as multi box it is showing all the values (but i guess only distinct values should be shown).Please see my calendar code and do the needful.

TIme filters like below

Starttime endtime

00:00 AM 00:00AM\

24:00 PM 24:00 PM

Please do the needful.

Regards

Thiru

Re: Date Range and time range is not working

sunny_talwar — Mon, 13 Mar 2017 10:41:23 GMT

Not sure I understand your question

Re: Date Range and time range is not working

Mon, 13 Mar 2017 10:53:47 GMT

Hi Sunny,

In my application i have added filters for startdate and enddate using variables .this is fine using this end user will select date range and analyze.

same way i need to add two filters for time which will having time period like

starttime and endtime(as shown in the attached).

This part i am not clear how to add time filters like 00:00 am,01:00 am ,02:00 am .....11:45 pm like that.

case1:

if end user select date range like 2010-01-01 to 2014-01-01 then it will be all the orders between these dates

case2:

end user selects date range like 2010-01-01 to 2014-01-01 and time range 08:00am to 10:00 am then

it is everydate 08:00 am to 10:00 am how many orders happend?

Hope you got the point.

Regards

Thiru

Re: Date Range and time range is not working

sunny_talwar — Mon, 13 Mar 2017 12:34:12 GMT

So you are looking to get time as an input from user also? Can you send the updated app where you imagine user input and what format?

Re: Date Range and time range is not working

Mon, 13 Mar 2017 12:41:07 GMT

Hi sunny,

Even i am also struggling to user input for time ,please check in attached image that is my requirement.

As mentioned above two cases required one is

1.Date range

2.time range

Ex:

1.Daterange:

2010-10-10 to 2014-10-10

means all orders between this range

2.time range

if we select time like 8 pm to 10 pm

then

in the date range

2010-10-10 to 2014-10-10

only between 8pm to 10 pm

Please do the needful

Regards

Thiru

Re: Date Range and time range is not working

sunny_talwar — Mon, 13 Mar 2017 14:46:06 GMT

May be like attached

Re: Date Range and time range is not working

Tue, 14 Mar 2017 08:23:06 GMT

Hi sunny,

I have added inline data as starttime and endtime .

Can you please modify the set analysis syntax and let me know.

Thanks

Thiru

Re: Date Range and time range is not working

Tue, 14 Mar 2017 08:38:41 GMT

Hi Sunny,

In addition to that please let me know

this

Sum({<Time={'>=(Starttime)<=(Endtime)'}>} country)

Thiru

Re: Date Range and time range is not working

sunny_talwar — Tue, 14 Mar 2017 09:20:29 GMT

Let you know what? Can you elaborate?

Re: Date Range and time range is not working

Tue, 14 Mar 2017 10:31:36 GMT

Hi Sunny,

This is the condition i need to put in the report for time range,if you open my attached application then i have created that starttime and endtime .

Your application it is input box which end user has to enter the values.

But when i use the condition it is not working might be datatype issue.Please check and do the needful.

Re: Date Range and time range is not working

sunny_talwar — Tue, 14 Mar 2017 10:34:42 GMT

May be this?

Count({<YearMonth={">=$(=Date(Vstartdate, 'YYYY/MM/DD h:mm:ss TT'))<=$(=Date(Venddate, 'YYYY/MM/DD h:mm:ss TT'))"}, Time = {"=Frac(Time) >= Starttime and Frac(Time) <= Endtime"}>} Country)

Re: Date Range and time range is not working

Tue, 14 Mar 2017 10:43:26 GMT

Hi sunny,

try to give the range 6 am to 8 am It is not working for me.

only 8 to 11 is working.

Thiru

Re: Date Range and time range is not working

sunny_talwar — Tue, 14 Mar 2017 10:47:37 GMT

This might be because of the rounding issue.... If you select 8:15, you should see two rows... or use this

Count({<YearMonth={">=$(=Date(Vstartdate, 'YYYY/MM/DD h:mm:ss TT'))<=$(=Date(Venddate, 'YYYY/MM/DD h:mm:ss TT'))"}, Time = {"=(Frac(Time) + 1/86400) >= Starttime and (Frac(Time) - 1/86400) <= Endtime"}>} Country)

Re: Date Range and time range is not working

Tue, 14 Mar 2017 11:06:11 GMT

Hi sunny,

I guess it is working.

and here mandotory we need to select both date and time.but for me two cases are requiered.

1.only date

2.date and time

so can i use

if(GetFieldSelections(Starttime,1,

Count({<YearMonth={">=$(=Date(Vstartdate, 'YYYY/MM/DD h:mm:ss TT'))<=$(=Date(Venddate, 'YYYY/MM/DD h:mm:ss TT'))"},

Time = {"=(Frac(Time) + 1/86400) >= Starttime and (Frac(Time) - 1/86400) <= Endtime"}>} Country),

Count({<YearMonth={">=$(=Date(Vstartdate, 'YYYY/MM/DD h:mm:ss TT'))<=$(=Date(Venddate, 'YYYY/MM/DD h:mm:ss TT'))"}>} Country)))

Regards

Thiru

Re: Date Range and time range is not working

sunny_talwar — Tue, 14 Mar 2017 11:11:07 GMT

Sure... give it a try

Re: Date Range and time range is not working

Thu, 23 Mar 2017 14:08:20 GMT

Hi Sunny,

I have chnged Starttime and endtime which are in link tab

from 01:00:00 AM to 1:00 AM

02:00:00 AM to 2:00 AM like that

then it is not working.Please so the needful

Regards

Thiru

Re: Date Range and time range is not working

sunny_talwar — Thu, 23 Mar 2017 15:22:13 GMT

Please do the needful by sharing a sample and pointing out what the issue is within your sample

Re: Date Range and time range is not working

Fri, 24 Mar 2017 07:42:12 GMT

Hi Sunny,

My apologies for not giving complete information.

Issue is As of now we are using StartTime and Endtime variables which are coming from inline data prseneted in link tab in the script format is

02:30:00 AM,

02:45:00 AM,

03:00:00 AM,

03:15:00 AM,

03:45:00 AM,

04:00:00 AM,

04:15:00 AM like that

here my Time column also loading using time(tempdate) as Time

so our expression is

if(isnull(GetFieldSelections(Starttime))=0 and isnull(GetFieldSelections(Endtime))=0,Count({<YearMonth={">=$(=Date(Vstartdate, 'YYYY/MM/DD h:mm:ss TT'))<=$(=Date(Venddate, 'YYYY/MM/DD h:mm:ss TT'))"},

Time = {"=(Frac(Time) + 1/86400) >= Starttime and (Frac(Time) - 1/86400) <= Endtime"}>} Country),

Count({<YearMonth={">=$(=Date(Vstartdate, 'YYYY/MM/DD h:mm:ss TT'))<=$(=Date(Venddate, 'YYYY/MM/DD h:mm:ss TT'))"}>} Country))

is working fine.

Now the issue is i have changed the Starttime and Endtiem format in link tab change s to

12:00 AM,

12:15 AM,

12:30 AM,

12:45 AM,

1:00 AM,

1:15 AM,

1:30 AM,

1:45 AM,

2:00 AM,

2:15 AM,

2:30 AM like this

after chnageing this format it is not working.

Please do the needful in changing the syntax according to the time format.

Regards

Thiru