topic Re: Date help in QlikView

Date help

QlikAngel — Wed, 07 Sep 2016 10:17:11 GMT

Hi,

My requirement is like below..

if event date = same day of event date and also next day before 8am then consider as same day of event date.

if entry done on 06th sep and also entry done on 07th sep till 8:00am then consider that date as 06th Sep only.

please help how to do.

Re: Date help

kaushiknsolanki — Wed, 07 Sep 2016 10:18:40 GMT

Hi,

Can you post some example data.

Regards,

Kaushik Solanki

Re: Date help

Anonymous — Wed, 07 Sep 2016 10:19:45 GMT

Do you mean evedate is varied from eg:

12:00 AM 09/07/2016 TO 8:00 AM09/08/2016............would be taken as 09/07/2016 ?

Re: Date help

effinty2112 — Wed, 07 Sep 2016 10:40:08 GMT

Hi Mayuri,

Try:

=Date(Num(Timestamp)-1/3)

Kind regards

Andrew

Re: Date help

QlikAngel — Wed, 07 Sep 2016 10:42:34 GMT

Yes exactly Alluraiah Allu

Re: Date help

Anonymous — Wed, 07 Sep 2016 10:43:13 GMT

in the load editor please try this

FYI :Attached the sample excel file worked.....

SOURCE:

LOAD

Time(Date,'HH:MM:SS') AS TIME,

Date

FROM

(ooxml, embedded labels, table is Sheet1);

OUTPUT:

LOAD *,

if(Date<>Previous(Date) and hour(TIME)<=8,Previous(Date),Date) as Event_Date

/***change the FORMAT of Event_Date to MM/DD/YYYY*******

Resident SOURCE;

DROP Table SOURCE;

Re: Date help

QlikAngel — Wed, 07 Sep 2016 10:48:04 GMT

if Event date is 07th Sep 2016 after 8:00am to 08th Sep 2016 8:00am , then that transaction should show in date as 07th Sep 2016 only

Re: Date help

Anonymous — Wed, 07 Sep 2016 10:50:20 GMT

we have to change NORMAL DAY range (12 am to 12 AM)..............to DAY(8 AM to 8 AM) ??

EDITED

FYI:

In my example :

9/7/2016 15:55

9/8/2016 3:55

Both will go to 09/07/2016 !!!!

Re: Date help

QlikAngel — Wed, 07 Sep 2016 11:10:05 GMT

Yah normal day range will be 8:00am today to next day 8:00am

Re: Date help

florentina_doga — Wed, 07 Sep 2016 11:44:41 GMT

try this

=date(if(frac(num(time(today())))<0.33333333333576,date(today())-1,date(gggg)))

Re: Date help

florentina_doga — Wed, 07 Sep 2016 11:45:54 GMT

sorry

=date(if(frac(num(time(today())))<0.33333333333576,date(today())-1,date(today())))

Re: Date help

QlikAngel — Wed, 07 Sep 2016 12:07:55 GMT

else part is not working

Re: Date help

Chanty4u — Wed, 07 Sep 2016 12:09:42 GMT

try below ,

as per florentina dogaru

=date(if(frac(num(time(today())))<0.33333333333576,date(today()-1),date(today())))

Re: Date help

florentina_doga — Wed, 07 Sep 2016 12:23:52 GMT

date must be timestamp

maybe this

=date(if(frac(num(time(now())))<0.33333333333576,date(now())-1,date(now())))

Re: Date help

Wed, 07 Sep 2016 12:44:37 GMT

Dear Florentina,

We are working on event date, hence now and today wont work on previous data.

If i am selecting 03-09-2016 I should get values from 03-09-2016 after 08 AM and 04-09-2016 before 8 AM.

If Event date is 07th Sep 2016 after 8:00am to 08th Sep 2016 8:00am , then that transaction should show in date as 07th Sep 2016 only.

Re: Date help

QlikAngel — Wed, 07 Sep 2016 12:52:50 GMT

Hi,

Now & today will work for current date..

my requirement is if Date is 04th September'16 then transaction if created after 08:00AM on 04th Sep'16 and on 05th Sep'16 if transaction happens before 08:00AM then it should consider in 04th September..

Re: Date help

effinty2112 — Wed, 07 Sep 2016 12:56:52 GMT

Hi Mayuri,

you only need to subtract 8 hours (= a third of a day) from your timestamp then convert to date

=Date(Num(Timestamp)-1/3)

Regards

Andrew