sum of hours
Andrea Masiero
Employee
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

Tagged:
0
Answers

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

0 
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

0
Categories
 All Categories
 2K Forums
 1.8K Platform
 159 Academy
 324 Resources
 1 Board Knowledge Base
 50 Best Practices
 49 HowTo Guides
 19 Board Advocacy Program
 191 Blog
 4 Groups Hub
 4 About Groups
 New Community Members
 DACH
 Japan
 4 Community Captains
 1 About Community Captains
 2 Meet the Community Captains
 1 Topics & Thought Starters
 Learn from the Board Captains
 Release Notes
 Academy
 2 Board Academy
 8 ILT/VILT Course Catalogue
 13 eLearning Course Catalogue
 4 Academy Forum
 1.2K Idea Exchange
 337 Partner Hub
 94 Support
 14 FAQ's
 Customer Support Portal
 54 Support Articles
 BEAP