Hello everyone,
I work as an HR Manager for a company. I have an Absence sheet where I record the daily absences of colleagues. My company's executives recently decided they would use the Barford Score System (which gives each colleague a score based on their total number of absences (due to sickness) and the occurrence of sickness. The Bradford score is the Total number of sick days * sick days occurencies^2. This Braford formula I would like to implement in a Query function. My formula so far looks like this: =UNIQUE(QUERY('Bradford Score_raw_DATA_2023/24'!B4:H, "SELECT E, SUM(G), SUM(H), SUM(G)*SUM(H)^2
WHERE E IS NOT NULL
GROUP BY E
LABEL E 'NAME', SUM(G) 'SICK DAYS (S)', SUM(H) 'SICK OCCURRENCES (D)', SUM(G)*SUM(H)^2 'BARDFORD SCORE'"))
Where in Bradford Score_raw_DATA:
- E is the colleague's name
- SUM(G) is the summed total days of sickness
- SUM(H) is the summed total sickness occurrences
In this sheet called Barford 2023/24 from D4 to G I would like to import the values and names. It is working perfectly If I don't add this part in the formula SUM(G), SUM(H), SUM(G)*SUM(H)^2 & SUM(G)*SUM(H)^2 'BARDFORD SCORE'"))
The problem is due to some reasons in the sheet named Bradford 2023/24 I have to add the mentioned part which will appear in column G, because with the query formula, I would like to keep the "
GROUP BY E" part, but also If I manually do the barford score in column G I can not (even tho with filter) order them by the Bardford scores. That is the reason I would like to calculate the score within the Query Formula, but it constantly gives this error:
Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " <ID> "AS "" at line 1, column 45. Was expecting one of: <EOF> "where" ... "group" ... "pivot" ... "order" ... "skipping" ... "limit" ... "offset" ... "label" ... "format" ... "options" ... "," ... "*" ... "+" ... "-" ... "/" ... "%" ...
Can anyone please help me with how to make this work?
Thank you so much for your suggestions and time!
Best,
Josh
I work as an HR Manager for a company. I have an Absence sheet where I record the daily absences of colleagues. My company's executives recently decided they would use the Barford Score System (which gives each colleague a score based on their total number of absences (due to sickness) and the occurrence of sickness. The Bradford score is the Total number of sick days * sick days occurencies^2. This Braford formula I would like to implement in a Query function. My formula so far looks like this: =UNIQUE(QUERY('Bradford Score_raw_DATA_2023/24'!B4:H, "SELECT E, SUM(G), SUM(H), SUM(G)*SUM(H)^2
WHERE E IS NOT NULL
GROUP BY E
LABEL E 'NAME', SUM(G) 'SICK DAYS (S)', SUM(H) 'SICK OCCURRENCES (D)', SUM(G)*SUM(H)^2 'BARDFORD SCORE'"))
Where in Bradford Score_raw_DATA:
- E is the colleague's name
- SUM(G) is the summed total days of sickness
- SUM(H) is the summed total sickness occurrences
In this sheet called Barford 2023/24 from D4 to G I would like to import the values and names. It is working perfectly If I don't add this part in the formula SUM(G), SUM(H), SUM(G)*SUM(H)^2 & SUM(G)*SUM(H)^2 'BARDFORD SCORE'"))
The problem is due to some reasons in the sheet named Bradford 2023/24 I have to add the mentioned part which will appear in column G, because with the query formula, I would like to keep the "
GROUP BY E" part, but also If I manually do the barford score in column G I can not (even tho with filter) order them by the Bardford scores. That is the reason I would like to calculate the score within the Query Formula, but it constantly gives this error:
Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " <ID> "AS "" at line 1, column 45. Was expecting one of: <EOF> "where" ... "group" ... "pivot" ... "order" ... "skipping" ... "limit" ... "offset" ... "label" ... "format" ... "options" ... "," ... "*" ... "+" ... "-" ... "/" ... "%" ...
Can anyone please help me with how to make this work?
Thank you so much for your suggestions and time!
Best,
Josh
