Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Date +1

I have the following issue, I have to increase the date by 1 when the time is > 23:59:59.

For example:

01/08/2011 00:00:23 needs to be 02/08/2011 00:00:23

I have the following expression but this changes al the dates:

if(NotLiveTxStart > '23:59:59', NotLiveTxStart +1, NotLiveTxStart)

What is missing here to get ONLY the dates higher than 23:59:59 to be increased?

Thanks!

Tags (1)
1 Solution

Accepted Solutions
Not applicable

Date +1

After all the best solution was to do this on database level. Qlikview uses a view to get the data, so I used a CASE statement there to solve this.

Thanks for the help!

12 Replies
lironbaram
Honored Contributor II

Date +1

hei

i didnt quite under stand what you want

may be you can explain better here are some dates tell me what the the date should be

01/08/2011 06:00:00

01/08/2011 00:30:40

01/08/2011 23:59:59

01/08/2011 00:00:00

Not applicable

Date +1

If the date is 01/08/2011 00:00:23 it should change to 02/08/2011 00:00:23

So the time stays the same but instead of august 1 it should be august 2.

lironbaram
Honored Contributor II

Date +1

well actuly every date expet to the 30/12/1899 is bigger then 23:59:59

you are checking dates agianst hours it not the same number

and 00:00:23 isnt bigger then 23:59:59it smaller

Not applicable

Date +1

But how do I solve this?

lironbaram
Honored Contributor II

Date +1

hei

can you post an excel table with example of the resaults you want

i dont realy understand what do you want to achive

Not applicable

Date +1

Let me first try to explain further.

It's about television shows that are archived after they have been broadcasted.

Say for example that a television show is scheduled for broadcast at August 1 23:59:00.

It actually starts at 00:00:20. That is technically August 2 early in the morning.

The data I now receive says August 1 00:00:20 which is earlier than the broadcast time of August 1 23:59:00.

To get accurate results in the report the date has to change to the next day when the actual start is after 23:59:59. Since it is in reality the next day.

lironbaram
Honored Contributor II

Re: Date +1

ok good explantion

but do you have in the record both the schedule time and the actual time

i think your expression should be somthing like this :

if(floor(NotLiveTxStart )=floor(ScheduleTime) and ( NotLiveTxStart -floor(NotLiveTxStart ))< ( ScheduleTime-floor(ScheduleTime)), NotLiveTxStart +1, NotLiveTxStart)

Not applicable

Date +1

Yes and the schedule time should stay the same regardless of the actual time.

Not applicable

Date +1

Hello,

One suggestion would be, As I understood you just want to add a Day to your date and nothing else should change. Then you need to separate the date with format and where its Day add +1 and try around this.

some thing like

if(Mydate = 'DD/MM/YYYY', Day(Mydate, 'DD'+1), MyDate)

Hope this helps.

Mady

Community Browser