topic Spread measure into hours in QlikView

Spread measure into hours

amit_shetty78 — Sun, 04 Sep 2016 15:19:04 GMT

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.

Re: Spread measure into hours

saimahasan — Sun, 04 Sep 2016 15:23:04 GMT

Hi Amit,

can you please post sample app

Re: Spread measure into hours

Anil_Babu_Samineni — Sun, 04 Sep 2016 15:56:13 GMT

What are these values?

40 120 30 60 10 - Task completed per hour

Re: Spread measure into hours

amit_shetty78 — Sun, 04 Sep 2016 16:07:36 GMT

@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.

Re: Spread measure into hours

Anil_Babu_Samineni — Sun, 04 Sep 2016 16:16:10 GMT

Oh ok, can you please share your application?

Re: Spread measure into hours

maxgro — Sun, 04 Sep 2016 18:20:07 GMT

try the attachment

Re: Spread measure into hours

MarcoWedel — Sun, 04 Sep 2016 23:02:04 GMT

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

Re: Spread measure into hours

Anonymous — Mon, 05 Sep 2016 11:54:24 GMT

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!!

Re: Spread measure into hours

amit_shetty78 — Mon, 19 Sep 2016 10:02:03 GMT

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.