topic Re: how to add time fields together in QlikView

how to add time fields together

Wed, 25 Nov 2020 16:16:04 GMT

I have a time field imported from excel that I want to add together to get a value. It is basically a call stats report. I am tying to add all the calls together and come back with a value in hours:Mins:Seconds

It is in this format in excel 12:04:15 AM. In excel sheet if I used to format :mm:ss I can add columns together.

Attached is the example. I am trying to add field Total Time. How can I do this in Qlikview ?

Re: how to add time fields together

Gysbert_Wassenaar — Wed, 28 Sep 2016 17:04:28 GMT

Can you explain what you're trying to do in more detail? Your excel file already contains a field totaltime. So why do you think you need to create that when it already exists?

In excel sheet if I used to format :mm:ss I can add columns together.

Which columns? I see only one column with values that look like times.

Re: how to add time fields together

Wed, 28 Sep 2016 20:43:49 GMT

Hi.. I want to be able to get weekly totals & daily totals. A user can have multiple numbers & calls .. I need to be able to sum the time field e.g 4 calls total talk time 0:30 .. 30 minutes

Re: how to add time fields together

MarcoWedel — Wed, 28 Sep 2016 22:32:48 GMT

Hi,

one solution might be:

tabCalls:
LOAD Date(Date#(xDate,'YYYYMMDD')) as xDate,
    full_name,
    Call_Type,
    totaltime,
    "[No name 1]",
    Extension,
    Date(Date#(weekinfo,'YYYYMMDD')) as weekinfo,
    xOrg2_NameCode
FROM [https://community.qlik.com/servlet/JiveServlet/download/1132676-247516/Test%20a.xlsx] (ooxml, embedded labels, table is Sheet1);

tabCalendar: 
LOAD *, 
    Day(xDate) as Day, 
    WeekDay(xDate) as WeekDay, 
    Week(xDate) as Week, 
    WeekName(xDate) as WeekName, 
    Month(xDate) as Month, 
    MonthName(xDate) as MonthName, 
    Dual('Q'&Ceil(Month(xDate)/3),Ceil(Month(xDate)/3)) as Quarter, 
    QuarterName(xDate) as QuarterName, 
    Year(xDate) as Year, 
    WeekYear(xDate) as WeekYear;   
LOAD Date(MinDate+IterNo()-1) as xDate 
While MinDate+IterNo()-1 <= MaxDate; 
LOAD Min(xDate) as MinDate, 
    Max(xDate) as MaxDate 
Resident tabCalls;

hope this helps

regards

Marco