Forum Discussion

IvanS's avatar
IvanS
Helper V
3 years ago
Solved

Work efficiency calculation

Hi guys,

 

I would need your help with calculating work efficiency for our workers. I have 2 tables - FACT_Tasks and DIM_Users

FACT_Tasks contains following fields:
- Task ID

- Working time (in days)

- Worker name

- Close date

 

DIM_Users contains following fields:
- Worker name

- Employment date

 

Now, what I need to calculate is YTD work efficiency by dividing cumulative sum of working time by nr. of working days until today with following condition:

- if the person joined before 1.1.2022, then to use as denominator NETWORKDAYS(DATE(YEAR(TODAY()), 1, 1), TODAY(), 1)

- if the person joined later than 1.1.2022, then to use as denominator NETWORKDAYS(Employment date, TODAY(), 1)

 

I am somehow struggling to create IF condition in measure, so any help is much appreciated!


Thank you
IvanS

Need Help
Tips and Tricks
  • Bifinity_75's avatar
    Bifinity_75
    3 years ago

    Hi IvanS , If I understood correctly, you have to create a measure similar to this:

    Result= SWITCH(
                TRUE(),
                'Your date column' <= DATE(2022,1,1), DIVIDE(sum('Working time'), NETWORKDAYS(DATE(YEAR(TODAY()), 1, 1), TODAY(), 1),"-"),
                'Your date column' > DATE(2022,1,1), DIVIDE(sum('Working time'), NETWORKDAYS(Employment date, TODAY(), 1),"-"),
                "")

    Best regards

     

2 Replies

  • Bifinity_75's avatar
    Bifinity_75
    Solution Sage
    3 years ago

    Hi IvanS , If I understood correctly, you have to create a measure similar to this:

    Result= SWITCH(
                TRUE(),
                'Your date column' <= DATE(2022,1,1), DIVIDE(sum('Working time'), NETWORKDAYS(DATE(YEAR(TODAY()), 1, 1), TODAY(), 1),"-"),
                'Your date column' > DATE(2022,1,1), DIVIDE(sum('Working time'), NETWORKDAYS(Employment date, TODAY(), 1),"-"),
                "")

    Best regards

     

    • IvanS's avatar
      IvanS
      Helper V
      3 years ago

      Thanks soooo much for help! Worked like a charm! 🙂 

       

      I just got info to calculate the work efficiency on monthly/quarterly basis. Could you please suggest what will be the way how to calculate nr. of working days in specific month/quarter to get the result?

       

      Example:
      Worker name - Period (ideally date hierarchy) - Work efficiency

      Worker 1 - January - Work efficiency 85%

      Worker 1 - February - Work efficiency 70%

       

      Thank you

      IvanS