sum of hours

Options
Andrea Masiero
Andrea Masiero Employee
Level 100: Foundations of Building in Board First Anniversary First Comment 5 Up Votes
edited November 2020 in Platform

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)

image



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:

Answers

  • Senne Vanstraelen
    Senne Vanstraelen Active Partner
    First Comment First Anniversary
    edited November 2020
    Options
    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
    Helmut Heimann Employee
    March Badge of the Month February Badge of the Month Community Captain April Badge of the Month
    edited November 2020
    Options
    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:


    image


    I hope this will give you a clue.

    Kind Regards,
    Helmut

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

Categories