1

I'm trying to create a metric focused on the maximum number of workdays and employees. Could you help me to figure out what I'm missing? I have the following:

  • A fact named Workday (values 1 and 0).
  • The dimensions attached to the fact: Company, Employee, Date

Companies are essentially our subsidiaries in different countries where different holidays are applicable, resulting in differences in maximum available Workdays.

In my report, I want to see metrics:

A: the number of Workdays per Employee in a selected time period

B: number of MAX Workdays any Employee had in the sae Company and in the same time period (basically how many workdays were available if a specific employee had worked on all possible days)

C: % of A/B

I have problems to create a metric for B where I want to find the maximum number of Workdays any Employee in the same Company had within a chosen time period.

The closest to what I need is when I define the metric B as follows:

SELECT MAX(SELECT SUM(Workdays) BY Employee WITHOUT PF EXCEPT Date (Worked Date)) BY Company ALL OTHER

However, as soon as I filter the report for any subgroup (specific employee, team, etc.) the value for metric B gets affected by the selection. For example, if I filter a single employee who only worked 10 days out of maximum 20, the value of metric B also shows 10.

Do you have any suggestions what am I missing?

I am aware of the fact that it might not be the best idea to determine max workdays based on what is the highest number days someone worked. Because it’s possible that maybe even that person with the highest number wasn’t available on all days. I have in my roadmap to move away from this approach and import the holiday patterns per company, but for now I would like to deal with this approach.

juan.cruz
  • 11
  • 1

1 Answers1

0

The WITHOUT PF needs to be applied to the outer MAX metric as well. Your metric should work when you move it at the very end of the metric:

SELECT MAX(SELECT SUM(Workdays) BY Employee) BY Company ALL OTHER WITHOUT PF EXCEPT Date (Worked Date)

Depending on your exact model, you may need to include the Company in your report.

Another solution (if you don’t have holiday calendars) can be to count working days as days where at least one Employee from the company was working (something like SELECT COUNT(Date (Worked Date)) BY Company, Date (Worked Date), ALL OTHER WHERE (SELECT SUM(Workdays)) > 0)

Tereza S.
  • 1