Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense 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