Countover on distinct values

Q&A
1

I have a dataset that records per month an employee for a specific company.

01/2022 | PERSON A | COMPANY 1
02/2022 | PERSON A | COMPANY 1
01/2022 | PERSON B | COMPANY 1
....

I would like to create a donut chart that shows how many companies have 0-10 employees, 11-50 employees, 51- 100 employees, …

I’ve created the following calculated field: employees per year and per company

countOver({policy number}, [{employment year}, {employer}], PRE_AGG)

And a second calculated field: employees per year and per company bin

ifelse(
    {employees per year and per company} <= 10.0, '0 - 10',
    {employees per year and per company} <= 50.0, '10 - 50',
    {employees per year and per company} <= 100.0, '50 - 100',
    {employees per year and per company} <= 250.0, '100 - 250',
    {employees per year and per company} <= 500.0, '250 - 500',
    {employees per year and per company} <= 1000.0, '500 - 1.000',
    '> 1.000'
)

Problem is that each employee is counted for each month in a year. So employees who have worked 12 months will be counted 12 times, employees who have worked 6 months will be counted 6 times.

I’ve tried to redefine my calculated field as follows, but then I get an error related to the group by level. I don’t understand why I get this error.

distinct_count({policy number}, [{employment year}, {employer}])
2

Do you get an error in the calculated field or the visual? And what is that error?

Also, to note you will need to use employment year and employer in your visual if you want to group it.

I would suggest moving this to SQL if you can.

3

Hi @Ivan_Eulaers ,
Could you change your first calculation to a distinct count as such:

distinctcountOver({policy number}, [{employment year}, {employer}], PRE_AGG)