Hi! I hope it’s ok to ask a MAQL question here. I am still learning to write the queries and I’m stuck with something. I am attaching a snippet of my LDM covering the relevant part for what I am working on. The JIRA dataset is collapsed because it has so many facts and attributes, which would add tons of irrelevant details. It is relevant to know that JIRA dataset holds information about employees: • Username - attribute which is an identifier for each employee • Company they work for (reference to Companies dataset) • Day of employment (reference to Worked Date) • Availability - A fact that the person was employed on a day (represented as value of 1) Working Days dataset holds a workday/holiday pattern for each company dictated by local employment regulations (1 if the day is workday, 0 if it’s not). What I am trying to do is to build a metric which eventually can be aggregated by company or even the whole group of companies, showing the number of full time employees (FTE) in a selected time period. My plan is to achieve that by finding: 1. Number of workdays for each employee in a chosen time period (SUM of Workdays in JIRA dataset for that person in the selected time period) 2. Number of workdays in the company of that person in a chosen time period (SUM of Workdays in Working Days dataset for the company that is associated with the person in the JIRA dataset) And then dividing the first number by the second. For a person that was employed throughout the chosen time period, the result is 1 because the amount of workdays for the individual person matches the number of workdays for their company. However, if the person only worked part of the period, then their FTE number is less than 1. I am struggling with handling the calculation for persons who have joined in the middle of the period. Check the second screenshot, representing one person per line. 14 and 15 of August on the screenshot represent a weekend, correctly showing that it was not a working day for the employee, nor the company. If you look at the 5th line, then you start seeing the problem. The line shows a person whose employment started on 16 August and this is correctly shown by the Employee WD metric, but the problem is that Company WD is not even shown for rest of the chosen time period. Meaning that if I add up the numbers for the person over the time period, I still get Employee WD matching the Company WD and the resulting FTE result is 1. I am thinking that this behaviour happens because the connection point between the person and the company only exists for the days for which I have records in the JIRA dataset. Is there a way to build a metric which would find the number of Company WD for the full chosen time period, not only for the days the person worked? I guess one way to achieve that is to populate the JIRA dataset with 0 values for the days when the person wasn’t employed, but is there perhaps a more elegant solution?

Hi @Allan Talver, Let me give you some hints which may help. There can be problem with “NULL arithmetics” in the expression:

Employee WD / Company WD

If there is no record for employee in Jira dataset the metric Employee for that date has probably NULL value. The result of division is: • 1/1=1 • 0/1=0 • null/1=null I suggest to wrap Employee WD by IFNULL function:

IFNULL(Employee WD,0) / Company WD

Query engine will interpret null value (missing data) as 0 and result of IFNULL(null,0)/1 should be 0. I suggest not to use Records of Jira in metric expression of Company WD. So that it returns value even for days for which none record exists. I suggest something like:

select sum(select ifnull(sum(Availability -Unpaid (Absence)/8),0)/sum(Workday) by Username, Date(Work Date))

Hi @Jakub Sterba, Thanks for the quick response. I will delve deeper into your suggestions in a moment, but I can comment on the “Records of Jira”. I used that as a link to match a Company for an employee (Username attribute in the Jira dataset). Basically I was trying to follow the logic explained in this article: https://help.gooddata.com/doc/enterprise/en/dashboards-and-insights/maql-analytical-query-language/maql-use-cases-and-tutorials/explicit-lifting

I see, Username is just in Jira dataset. Let me think about it again 🙂

Hmm, let me try that.

so in a context of a month, total number of workdays of a company can be 20 and this value will be used for each record in Jira dataset of all the company employees

Yes, the User can exist in various companies over time (but not at the same time). Not sure if that will invalidate your approach? Unfortunately I still feel quite “green” with the MAQL things, so I have to first try it out to be able to evaluate what will happen. 😄

In that case it may be the way to count users by company and then sum it up

select sum(select FTE_defined_above by Company)

User would be not counted as FTE if the user worked few days for one company and rest of the month for another even if he worked in all the days.

Thanks for your help with this @Jakub Sterba! I have to stop for today, but I definitely got some ideas that I can continue working with on Monday (if not sooner). But a general question, how can one build up their knowledge about more advanced uses of MAQL? I have gone through GoodData documentation as well as the University courses, but I still feel like something is missing and on my own I often get stuck, at some points more or less just trying to combine different statements together to see if anything will take me closer to what I need. I am sure that this is not the most efficient way to build up the experience. Is there another course or other resources that could help me in this? 🙂

Hi did you try this?

select count(Username) where 
(select
  (select sum(Availability - Unpaid (Absence)/8 ))
  /
  (select max(select (select sum(Workday) by Company) by Records of Jira))
by Username)=1

Just out of my curiosity - would not it be more efficient to extend the model by a dataset, which would contain number of working days (based on the regulations) per country? This dataset would be connected by country attribute with the rest of the model. You would not have to calculate number of working days from Jira issues dynamically. Btw. this dynamic calculation seems also to be a potential performance issue, imho.

Hi @Jakub Sterba I checked this once again and actually you were correct, it does seem to work. I had a problem with underlying data which led me to believe that the metric is showing an incorrect result for some employees. But actually the data was broken. However, I have two follow up problems/questions. Currently the metric looks like this:

I think approach with lifting is not so flexible if you need to slice the metric by different date attributes, but I think I have found a better solution. I suggest to use following metrics: Sum of Availability:

select sum(Availability)

Work days:

select sum(Workday) by all Username

FTE:

select Sum of Availability / Work days

The trick with “by all Username” will compute the sum(Workday) in context of insight (Company, Month/Year, Username) without the Username (Company,Month/Year) and result will be repeated for each value of Username. Without the “by all Username” the sum(Workday) cannot be broken down by Username. The value will be available in insight only for the months where exists relation between Username, Month, and Company. See example with metrics below. Company A has working days Mon-Fri and Company B Sun-Sat.

Oh, wow! Thanks @Jakub Sterba! I’m recreating and testing it in our environment now. But I have one question about one pattern I see in your metrics. Could you explain why you decided to use the count function? Is the intention to only count the employees who have their FTE number equal to 1 (and ignore others, like part timers, etc.)? In my case I would have to include all part timers as well and the end result should essentially be a number of “full time equivalents”. For instance, a company has only two employees who only work on half of the days, giving 0.5 load for both of them, should be added, resulting in 1 FTE for that company. So I believe that in my case I should be using SUM, instead of COUNT.

try to list them in the metric definition:

select sum(Workday) by all Username,Employee Status, ...

Hmm, then I get the “Sorry, we can’t display this insight” message

sorry, my bad

Oh, right. Let me try that 🙂

there is by all in all other dimensions

It does appear to work now when I listed the attributes one by one. I’ll do some checks to verify if the numbers make sense.

I think Records of Jira won’t work because it will ignore also slicing by Company and date attributes

Aha, ok. Thanks! Probably I wasn’t clear enough in my explanation. The intention is to get a sum of the contribution of all employees (even if they are fractional)