Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

how to add time fields together

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 ?

3 Replies
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
Not applicable
Author

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

MarcoWedel

Hi,

one solution might be:

QlikCommunity_Thread_234542_Pic2.JPG

QlikCommunity_Thread_234542_Pic1.JPG

QlikCommunity_Thread_234542_Pic3.JPG

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