BAQ Calculated Field - Count True Values

Epicor ERP 10
,
1

Good afternoon Epicor Community!
I’ve created a nice BAQ that summarizes the employee information, including new hires and terminated employees. I’ve created a calculated field (boolean type) that indicates whether an employee was hired in 2023, but am trying to get a count of these values that are true. I’ve tried a few different ways -

-sum(case when PREmpMas.HireDate >= ‘01/01/2022’ AND PREmpMas.HireDate <= ‘12/31/2022’ then 1 else 0 end) over (partition by PREmpMas.Company)

-count(case when PREmpMas.HireDate >= ‘01/01/2022’ AND PREmpMas.HireDate <= ‘12/31/2022’ then 1 else 0 end) over (partition by PREmpMas.Company)

-count(case when HireCount2022 = 1 then 1 else 0 end) over (partition by PREmpMas.Company)

There are a few other ways I’ve tried, but these methods do not return accurate counts, and I can’t understand what the Calculated Field thinks it’s doing, but none are the actual count. I have to do this for 2022 & 2023 Hires and 2022 & 2023 Terms.

I’ve read others solving this through Sub-Queries, but this would take a while. I was wondering if there is a way I can get an accurate count of values = true using a Calculated Field?

Thanks in advance for any suggestions or help your provide!

2

Hi Mike,

This has always been successful for me.

2 Likes
3

Try this on the TOP level.

This is not for the same table, but I am hoping it makes sense for your situation.*

select emp.LastDate,
year(emp.LastDate) as YEAR,
count(*) over (partition by year(emp.LastDate)) as COUNTbyYEAR
from Erp.UserFile as emp

The YEAR and COUNTbyYEAR will be repeated for each record.

Hard to determine how to make it “better” based on limited information of BAQ.

1 Like
4

You could add a Row Number column to the BAQ as a calculated field as follows:
“row_number() over(order by ExtPRPayClass.Description)”
The “Order By” field (“ExtPRPayClass.Description” in the example above) is that field by which the results are sorted. I typically put this field at the start of the table, then click Ctrl End to go to the end of the list and note the last row number, and there’s your count.

1 Like
5

Thanks everyone for your suggestions, I learned some new tricks along the way.
I ended up creating 4 additional subqueries that produced the results I was looking for.
My boss was pleased with it, so I appreciate you all helping me get over that hurdle!

2 Likes