Calculation of a row value by the Grand total for that selection

Hi!

I have a situation where I need to do a calculation of Allocated amount which is based on a formula like this: Hours of the employee for a particular Project divided by the grand total of hours for that employee for any selected date range and this multiplied by the rate of employment. I am not able to get the Grand Total aligned dynamically against each row so I can use it for my calculation. Basically I have a table like below:


How do I achieve this either in Beast mode or MYSql if its possible, I am not able to get the 56 total to use on my formula.


Help will be greatly appreciated.


Thanks

Answers

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    What you're describing is a window function. If you abstract your problem and google "percent of total" SQL, you'll find loads of solutions.

    You'll have to ask your CSM or support to enable "Window functions in Analyzer" but after that

    sum(sum(hours_worked)) over () will give you the 56

    https://www.youtube.com/watch?v=ZPf41Fjn1H8&t=10s

  • Thanks so very much , that resolved my issue.


    Thanks