Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Connect 2026! Turn data into bold moves, April 13 -15: Learn More!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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.

1 Solution

Accepted Solutions
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

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')


talk is cheap, supply exceeds demand

View solution in original post

7 Replies
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

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.


talk is cheap, supply exceeds demand
Not applicable
Author

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
Partner - Champion III
Partner - Champion III

Can you post a qlikview document that demonstrates the problem?


talk is cheap, supply exceeds demand
Not applicable
Author

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
Partner - Champion III
Partner - Champion III

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')


talk is cheap, supply exceeds demand
Not applicable
Author

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
Partner - Champion III
Partner - Champion III

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.


talk is cheap, supply exceeds demand