Spread measure into hours

[amit_shetty78]

Hi,

I have a scenario where I need some assistance with:

I have a measure that shows me the number of tasks completed between a start and an end time. Something like:

Start Time     End Time     Tasks Completed

4:40:00 PM     6:10:00 PM     180

6:25:00 PM     6:52:00 PM     10

7:30:00 PM     8:05:00 PM     70

I want to create an o/p such that I can show for each hour how many tasks were completed. Like:

4          5          6          7          8          - Hours

40        120     30         60        10        - Task completed per hour

Appreciate any help on this.

Thanks. 

[saimahasan]

Hi Amit,

can you please post sample app

[Anil_Babu_Samineni]

What are these values?

40        120     30         60        10        - Task completed per hour

[amit_shetty78]

@Anil,

That is the average number of tasks completed per hour.

For E.g., between 4:40:00 PM   and  6:10:00 PM,     180 tasks were completed.

That's breaks up to 180 tasks completed in 90 mins. Which means 2 tasks completed every minute.

So the split is, between 4:00 - 5:00 PM, 20 mins were utilized, therefore 20*2=40 tasks completed in this hour.

Between 5:00 PM - 6:00, 60 mins were utilized, therefore 60*2=120 tasks completed.

and the remaining 20 tasks were completed between 6:00 PM - 6:10 PM.

Similar calculations for each hour would produce the values mentioned earlier.

Hope this clarifies.

Thanks.

[Anil_Babu_Samineni]

Oh ok, can you please share your application?

[maxgro · Accepted solution]

try the attachment

[MarcoWedel]

Hi,

maybe one solution could be:

//sample data generation. replace with your source table
tabSampleData:
LOAD *,
    Ceil(Rand()*([End Time]-[Start Time])*240) as [Tasks Completed];
LOAD *,
    Time(Time#(Time([Start Time]+Rand()*(1-[Start Time]),'hh:mm'),'hh:mm'),'hh:mm:ss TT') as [End Time];
LOAD RecNo() as ID,
    Time(Time#(Time(Rand(),'hh:mm'),'hh:mm'),'hh:mm:ss TT') as [Start Time]
AutoGenerate 100;

//calculate tasks per minute
tabTaskMinute:
LOAD ID,
    Time(Time#(Time([Start Time]+(IterNo()-1)*'00:01','hh:mm'),'hh:mm'),'hh:mm:ss TT') as Time,
    [Tasks Completed]/([End Time]-[Start Time])/24/60 as [Tasks per Minute]
Resident tabSampleData
While [Start Time]+(IterNo()-1)*'00:01' < [End Time];

//time table
tabTime:
LOAD Time,
    Hour(Time) as Hour,
    Minute(Time) as Minute;
LOAD Time(Time#(Time((RecNo()-1)*'00:01','hh:mm'),'hh:mm'),'hh:mm:ss TT') as Time
AutoGenerate 1440;

hope this helps

regards

Marco

[Anonymous]

HI Amit,

Try this code:

Intervals:
load *,
     If(Hour(Start) = Hour(End),Minute(Interval(End - Start,'mm')),
         If(Hour(Start+MakeTime(IterNo() -1)) = Hour(Start),60-Minute(Start),
             If(Hour(Start+MakeTime(IterNo() -1)) = Hour(End),Minute(End),60)))*Task_X_Min     as Task_X_Hour,
     Hour(Start+MakeTime(IterNo() -1)) as Hour
While Hour(Start +  MakeTime(IterNo() -1)) <= Hour(End);
load 
    *,
    (Minute(Interval(End - Start,'mm'))+Hour(Interval(End - Start,'hh'))*60) as Min,
    Tasks / (Minute(Interval(End - Start,'mm'))+Hour(Interval(End - Start,'hh'))*60) as Task_X_Min
While Start +  MakeTime(IterNo() -1) <= End;    
load * inline [
Start,   End,    Tasks 
4:40:00 PM,     6:10:00 PM,     180
6:25:00 PM ,    6:52:00 PM ,    10
7:30:00 PM  ,   8:05:00 PM  ,   70
];

Output:

Formated output:

Regards!!

[amit_shetty78]

Thank you maxgro, Anil Babu, Marco and Manual for the help on this.

I used the suggestion provided by maxgro initially and was able to implement the required logic.

Thank you again.