How to Calculate Historical Headcount Based on Current Data, Admissions, and Terminations?

Q&A
, , ,
1

Hi there, apologies in advance if this is a beginner question. I’m currently stuck and decided to reach out to the community for help.

I have the following table structure:

employee_id | dperiod | info_type  
1           | 202508  | headcount  
2           | 202508  | headcount  
3           | 202508  | headcount  
4           | 202508  | headcount  
5           | 202508  | headcount  
6           | 202508  | headcount  
7           | 202508  | headcount  
8           | 202508  | headcount  
10          | 202508  | headcount  
11          | 202508  | headcount  
13          | 202508  | headcount  
7           | 202508  | admission  
8           | 202508  | admission  
9           | 202508  | termination  
10          | 202507  | admission  
11          | 202507  | admission  
12          | 202507  | termination  
13          | 202506  | admission  
14          | 202506  | termination

This table is a union of:

  • the current headcount snapshot (info_type = headcount),
  • the admission history (info_type = admission),
  • and the termination history (info_type = termination).

What I’m trying to achieve is a bar chart that shows the historical headcount per month (i.e., per dperiod), going backwards from the most recent data.

For example, the headcount for 202506 would be calculated like this:

HC(202506) = HC(202508) 
            - SUM(Admissions from 202507 to 202508) 
            + SUM(Terminations from 202507 to 202508)

My main output would be something like this:

ff2fbdc5-eb26-4b49-a8fa-c76f155b36e0
ff2fbdc5-eb26-4b49-a8fa-c76f155b36e01580×980 20.8 KB

So far I’ve only achieved to calculate the headcount for 202508, since my headcount info only has 202508 on dperiod, is there a solution for me?

Thank you very much!

2

Hello @Enzo , welcome to the Quick Sight community!

I’m curious if it would be possible to only count the employee_id field where info_type = ‘headcount’.

So something like this:

distinctCountOver(
  ifelse(info_type = 'headcount', employee_id, NULL),
  [dperiod], 
  PRE_AGG
)

Let me know if that works!

3

Hi @Enzo

It’s been a while since we last heard from you. If you have any further questions, please let us know how we can assist you.

If we don’t hear back within the next 3 business days, we’ll proceed with close/archive this topic.

Thank you!

4

Hi @Enzo

Since we have not heard back from you, I’ll go ahead and close/archive this topic. However, if you have any additional questions, feel free to create a new topic in the community and link this discussion for relevant information.

Thank you!