topic Re: Dates remove in QlikView

Dates remove

qlikview979 — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

Please find the attached file.

loveisfail‌stalwar1‌avinashelite‌@

I have two columns like Start Date,End date like number of rows but i want to remove Saturday,Sunday Dates.

I want show to only Monday to Friday dates in List box.

Regards

Mahesh

Re: Dates remove

qlikview979 — Fri, 22 Dec 2017 04:36:08 GMT

sorry data missing

PFA

Re: Dates remove

luismadriz — Fri, 22 Dec 2017 04:45:03 GMT

Hi,

This should help you. Do this with each list box

=Aggr(Only({<[Start Date]={"=Num(WeekDay([Start Date],0))<5"} >} [Start Date]),[Start Date])

Regards,

Luis

Re: Dates remove

luismadriz — Fri, 22 Dec 2017 04:46:39 GMT

This show the reduction for Start Date for example:

Re: Dates remove

prma7799 — Fri, 22 Dec 2017 04:47:09 GMT

Like this???

Re: Dates remove

prma7799 — Fri, 22 Dec 2017 04:52:28 GMT

Please check this??

function:networkday

Re: Dates remove

Anil_Babu_Samineni — Fri, 22 Dec 2017 05:07:00 GMT

I am sure, you will get bulk thread which are opened and status got closed related same, Just start community before come to thread.. Good luck

Re: Dates remove

qlikview979 — Fri, 22 Dec 2017 05:41:55 GMT

Sorry all ,i not am getting correct output.

Here i am sending my original data.

I have 4 columns like

Event start date ,

Event start time,

Event Cancel date

Event Cancel time

here i need difference between

Event start date &Event start time - Event Cancel date & Event Cancel time. here i want remove the weekend days(Saturday ,Sunday)

ex:-

10/13/2017

23:00:00

10/16/2017

1:00:00

in this example Event start date &Event start time is 10/13/2017 23:00:00 and 14th,15th, is (Saturday,Sunday) no need to calculate this

and Event Cancel date& Event Cancel time 10/16/2017 1:00:00 A.M

Here example result is 2 hours.

Regards

Mahesh

Re: Dates remove

Anil_Babu_Samineni — Fri, 22 Dec 2017 06:10:40 GMT

May be this?

NetWorkDays(TimeStamp(Date#([Event start date] & [Event start time],'MM/DD/YYYYhh:mm:ss')),

TimeStamp(Date#([Event Cancel date] & [Event Cancel time],'MM/DD/YYYYhh:mm:ss')))

NetWorkDays(Date(Date#([Event start date] & [Event start time],'MM/DD/YYYYhh:mm:ss'),'DD-MM-YYYY'),

Date(Date#([Event Cancel date] & [Event Cancel time],'MM/DD/YYYYhh:mm:ss'),'DD-MM-YYYY'))

Re: Dates remove

qlikview979 — Fri, 22 Dec 2017 06:30:09 GMT

Hi anil,

Its not showing correct output.

Regards

Mahesh

Re: Dates remove

Anil_Babu_Samineni — Fri, 22 Dec 2017 06:35:46 GMT

I just opened your excel, In excel the format looks like time period, may be try this? I don't have software to load your source

NetWorkDays(Date(Date#([Event start date] & [Event start time],'MM/DD/YYYYhh:mm:ss TT'),'DD-MM-YYYY'),

Date(Date#([Event Cancel date] & [Event Cancel time],'MM/DD/YYYYhh:mm:ss TT'),'DD-MM-YYYY'))

Re: Dates remove

qlikview979 — Fri, 22 Dec 2017 06:45:33 GMT

Thanks for your help Anil.

not showing data in list box.