topic Re: divide the time per month in QlikView

divide the time per month

A-T — Mon, 21 Nov 2022 12:55:31 GMT

Hello all,

Thanks for the effort, i need support to calculate the time "difference between 2 dates" and divide the result per month

for example

ticket "reported date" 18-Jul-22 04:37 PM

Closed"WO_Act finish" 05-Sep-22 12:00 PM

the result should be 48.8 days = 1171.38Hrs to close

Now i need to divide this value so that, each month present the corresponding time spent

meaning that we spend total (1171.35hrs "319.4 Hrs in july-22" and "744Hrs in august-22" and "108hrs in September-22"

Thanks in advance

Re: divide the time per month

MarcoWedel — Mon, 21 Nov 2022 21:22:44 GMT

Hi,

one example that might work for you:

table1: LOAD RecNo() as ID, Timestamp#(Reported_Date,'DD-MMM-YY hh:mm TT') as Reported_Date, Timestamp#(WO_ACTFINISH,'DD-MMM-YY hh:mm TT') as WO_ACTFINISH Inline [ Reported_Date, WO_ACTFINISH 18-Jul-22 04:37 PM, 05-Sep-22 12:00 PM 19-Jul-22 05:43 AM, 21-Aug-22 12:00 PM 02-Aug-22 12:34 PM, 22-Sep-22 02:14 PM 12-Aug-22 11:11 AM, 22-Aug-22 11:11 PM 13-Aug-22 10:01 AM, 22-Dec-22 01:10 PM ]; table2: LOAD *, Interval(To-From,'hh:mm') as Duration; LOAD ID, MonthName(Reported_Date,IterNo()-1) as MonthYear, Timestamp(RangeMax(Reported_Date,MonthStart(Reported_Date,IterNo()-1)),'DD-MMM-YY hh:mm TT') as From, Timestamp(RangeMin(WO_ACTFINISH, MonthStart(Reported_Date,IterNo() )),'DD-MMM-YY hh:mm TT') as To Resident table1 While MonthStart(Reported_Date,IterNo()-1) <= WO_ACTFINISH;

Re: divide the time per month

A-T — Tue, 22 Nov 2022 07:29:21 GMT

Hello marco,

thanks for the great support i've tried the example looks good if I have a limited data "rows" unfortunately is not the case

more explanation of what i am looking for

Reported date: indicate the time ticket created
Ticked has different status (New/in progress/ closed)
WO_ Actual finish: always empty unless the ticket is closed

now i need to calculate the time spent (TTC) to close the ticket

If the ticket closed then TTC= WO Act finish - Reported date

if the ticket in new/progress and since the WO act finish empty then TTC =end of month - reported date

and if we move to the next month and ticket still open then TTC will keep counting from the beginning of next month until we have time in WO act finish then TTC will equal WO Act finish-reported date

TTC result should be divided per month/week "reporting period" as sometime the ticket opened 28th of the month and closed 5th of next month so I need to show that I've spent 24 in this month, 36 hrs. next month and so on

Thanks for the great support hopefully this clarifies more.

BR,