Why adding a column in my table makes all go wrong ?

[#1]

Hello everyone,

I have a table like this :

Event_id;      timestamp_beginning        ;        timestamp_end           ;         delay_event (calculated field in minutes)

1                    16/10/2014  19:42:00                17/10/2014  18:02:00            1340   

There is one row for each event_id.

In my QV application, I have created a text zone where I have entered sum(delay_event) and I do get a result which looks ok.

I wanted to do a graph per month/week with a master calendar. (A calendar which contains 24 rows per day since a date I have specified).

But when I add  Floor( timestamp_beginning, 1/24) as Date in my table  in order to link with the master calendar, the result of "sum(delay) goes wrong... Even if I pout the calendar in comment  (and so my table is not linked to another), I still get a wrong result.

What can explain that ??

Thank you for your help

Have a good day.

[Gysbert_Wassenaar]

If your calendar table contains dates and your other table contains timestamps then only the timestamps with 00:00:00 as time part will be linked to the calendar table.Splitting the timestamp into a date and a time field should help:

LOAD ...other fields...,

     date(floor(MyTimeStamp),'DD-MM-YYYY') as MyDate,

     fract(MyTimeStamp) as MyTime

FROM ....

Change the date format MM-DD-YYYY to whatever format you prefer.

[#3]

Thank you for your reply Gysbert.

Sadly, the problem also occurs when my table is not linked to the calendar table (I have changed the name of the date field so it doesn't match any of the fields of the calendar table).

So if I sum up, when I do CTRL+T, I can see the table is "alone", linked to no tables.

If I do a table in the application with the fields:

Event_id;      timestamp_beginning        ;        timestamp_end           ;         delay_event (calculated field in minutes)

1                    16/10/2014  19:12:00                16/10/2014  19:42:00            29  

then I make a textbox with the formula : =sum(delay_event)

Then if I reload :

TheTable:

LOAD Event_id,

     timestamp_beginning        ,

     //Floor( timestamp_beginning        , 1/24) as Date2,

     timestamp_end           ,

    delay_event

FROM

If I filter on event_id =1, I do get 29 in the textbox, but if I remove the comment for Date2, in the textbox, I have 0,0207...

Really don't get why because my table is not linked to any other

Thank you

[Gysbert_Wassenaar]

Can you post a qlikview document that demonstrates the problem?

[#5]

Hi Gysbert,

Here it is.  So yoou can see the table with the value : 29  and in the textbox, I have entered sum(...) and I get 0,02...

Thank you for your help

[Gysbert_Wassenaar · Accepted solution]

Charge_durée_branchement is probably a time interval value formatted as minutes. The numeric value is 0,02... You can check this by changing the Number format in the listbox. If you want the sum expression to be displayed in minutes then you'll have to change the number format to Interval. In your text box you can change the expression to: = 'Sum = ' & interval(sum(Charge_durée_branchement),'m')

[#7]

Hi Gysbert,

Thank you, you are right. Charge_durée_branchement was calculated as Interval(stopped_at -started_at ,'mm').

I understand ! It's just aboout formating. The 0,02 is in "days" ( i mean the unit of date in qlikview), so when I do 0,02*24*60, I do get 29,8 minutes.

Thank you very much ! 

Have a good day

Laura

[Gysbert_Wassenaar]

The interval function merely formats a number as a time interval. The first parameter is a number, the second parameter is the format string. You can find more information in the help file.