sum of hours

Andrea Masiero

Hello all.

I need your help trying to see how I can model a specific request.

I need to create a dataview storing hours (see pictures here below)

By row I have the agent, by column the day of the week (m,t,w,t,f,s,s), data are the number of HH:MM the agent worked that day.

At a certain point I need to sum all the hours in this dataview (in the picture here above I sum up 655 hours and 2 minutes).

Do you already done something like that? If yes, date cubes? Doubtful…

Entity with the minutes from 00:00 to 23:59? Once I got this, how to sum it….

 

How would you do that?

Any idea and/or suggestion is welcome.

------------------------------
Andrea
------------------------------

Senne Vanstraelen

Andrea

Can't you store the base values for the hours spent as a # of minutes?

For example Service Agent 101 has 07:17 on Monday. 
This would mean a total of 7*60+17=437 mintues. 

This value can be stored in a cube and can be summed using default board logic. 

The only thing left to do, is create an algorithm in your dataviews which converts the # of minutes to the format you want (07:17).

Floor(a/60,0)&":"&((a/60)-FLOOR(a/60))*60

Would this be a possible solution for you?

Regards

------------------------------
Senne Vanstraelen
Senior Consultant
xB4 Consulting
Belgium
------------------------------
-------------------------------------------

Helmut Heimann

Hi @Andrea Masiero,

thanks for reaching out. Your aim is not a trivial one.

Since Board treats even the entity codes like text, you would not be able to sum up the members easily.
You would need to add a block for each hour/minute block that multiplies the entity's code by 1 to make it addable. I think that's not feasible.

So, I would propose another option: Converting the hours/mins to decimal values like 01:30 -> 1.5 during the load process via ETL.
This will give you the opportunity to calculate in your dataview and Board is also able to use all arithmetic functions available.
You'd get something like this for the first line of your example:

I hope this will give you a clue.

Kind Regards,
Helmut

------------------------------
Helmut Heimann
Senior Consultant
Board Community
Germany
------------------------------
-------------------------------------------