I want to get the count of employees that were active on a specific date. For instance,
count number of employees where status was “Active” on 1/1/2021.
To take it a step further, I want to get a count of how many employees were active at the beginning of the month. How could I create that? For instance,
count number of employees that were in status active at beginning of month.
Of course. One way is to combine ifelse and count in a calculated field:
count(
ifelse(
{date} = parseDate('2021-01-01'),
{employee_id},
NULL
)
)
Here I hard coded the date to ‘2021-01-01’ but of course you could compare with a parameter or another field. In order to compare with beginning of the month, you could just check that the date is the begining of the month using:
count(
ifelse(
{date} = truncDate('MM', {date}),
{employee_id},
NULL
)
)
Then if you put the field above as a value in a table visual, grouped by the date field (with MONTHLY aggregation), you will get the active employees at the beginning of each month.
Where do I add in the condition that the employees need to be in “active” status? I also want to make sure employees from the previous months are included in this.
Ok if you have another field status whose value needs to be Active then you could simply include it as follows:
count(
ifelse(
{date} = truncDate('MM', {date}) AND {status} = 'Active',
{employee_id},
NULL
)
)
2 Likes