topic Date +1 in QlikView

Date +1

Tue, 09 Aug 2011 12:35:22 GMT

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!

Date +1

lironbaram — Tue, 09 Aug 2011 12:41:49 GMT

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

Date +1

Tue, 09 Aug 2011 12:45:51 GMT

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.

Date +1

lironbaram — Tue, 09 Aug 2011 12:52:42 GMT

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

Date +1

Tue, 09 Aug 2011 12:56:19 GMT

But how do I solve this?

Date +1

lironbaram — Tue, 09 Aug 2011 13:01:34 GMT

hei

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

i dont realy understand what do you want to achive

Date +1

Tue, 09 Aug 2011 13:15:56 GMT

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.

Re: Date +1

lironbaram — Tue, 09 Aug 2011 13:18:39 GMT

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)

Date +1

Tue, 09 Aug 2011 13:21:35 GMT

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

Date +1

Tue, 09 Aug 2011 13:23:49 GMT

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

Re: Date +1

ToniKautto — Tue, 09 Aug 2011 13:24:37 GMT

What you need to keep in mind is that the time is presented as the decimal part of a number, so if you are about to compare numbers you need to convert the values to be compared to similar values. The decimal value represents the fraction of 24 hours that have passed.

So if you are calculating seconds the decimal corresponds to ( (H * 60 * 60) + (M *60) * (S) ) / (24 * 60 * 60)

which for 23:59:59 will be (82800 + 3540 + 59) / 86400 = 86399 / 86400 = 0,99998843...

As you can see there is no value larger than this on the clock, as 24:00:00 equals 00:00:00 and the decimal 0,0

Either way you setup your logic you only need to keep the decimal presentation in mind, and use the Time() and Time#() functions to format your data if required in the expressions. If you have a timestamp then the time functions will basically remove the integer part of the data so that you get the decimal time presentation.

Not sure what your value NotLiveTxStart contains, but if it is a TimeStamp value then you will increase it's date part by a day when you make +1 since the date is the integer part of the numeric value in the TimeStamp. If this is your problem, the make sure that the field is loaded as a TimeStamp by applying TimeStamp() and/or TimeStamp#() in the script.

Re: Date +1

lironbaram — Tue, 09 Aug 2011 13:32:53 GMT

hei

attach is an example

hope its helps you

Date +1

Thu, 11 Aug 2011 07:28:20 GMT

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!