Show headcount - have Start and End dates only

Question & Answer
, ,
1

Hi all,

I have a dataset with expat employee IDs, Contract start and end dates. I should be able to tell how many expats are on board until 2030 at any given date.
I want to visualize this on a continuous timeline with bars, but I have no idea how to generate datapoints between start and end.
Also tried to use a data scaffold table containing all dates until end of 2030, but not able to define relationship clauses that would help.

Any ideas?

EXAMPLE

ID Start Date End Date
1 2021.10.01 2023.12.01
2 2021.10.01 2023.09.01
3 2022.07.01 2024.03.01
4 2022.04.01 2024.06.01
5 2024.01.01 2026.06.01
6 2021.10.01 2023.09.01
7 2021.10.01 2023.09.01
10 2022.01.01 2023.09.01
11 2022.01.01 2026.06.01
12 2022.04.01 2026.06.01
14 2023.03.01 2026.06.01
16 2023.03.01 2026.06.01
31 2023.05.01 2030.12.01
32 2023.05.01 2030.12.01
2

Hello Norbert,

I believe you would need to fix the date field on the backend itself or during the data prep to have a single date field. or may be union of both date to include all possible data points.

you may refer to this old post and get some ideas -Count of active events over time - #7 by mchafik

Hope this helps.

Cheers,
Deep

2 Likes
3

I’ll try that, thank you! :slight_smile:

Update: I had to explode the rows to generate as many rows as number of months were between the start and end dates for each employee.
It does create duplicates, but this can be handled in QS if needed.

So, thanks for the tip!

1 Like