- A fact named Workday (values 1 and 0).
- The dimensions attached to the fact:
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 am struggling 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.
Any suggestions, what am I missing?
PS! 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 it is what it is. And I feel that making this metric work with the current data is a good opportunity to learn MAQL.
I have to admit that using different granularity and filtering statements in MAQL is still not completely clear for me. So another closely related question, is there any good learning material available about MAQL? I have gone through the University courses, but they are quite basic. On the other hand, the documentation pages on MAQL are very detailed and technical, but I feel that some “glue”is missing to help understand how these detailed concepts can be used together.
Best answer by Petr OlmerView original
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).