Dealing with Start and enddate

Q&A
,
1

I have a dataset that includes start_date and end_date for a job—similar to an HR dataset. For example, if a job was posted on February 1st and expired on December 1st, it was active across all months from February through December.

I want to create a monthly breakdown to classify “advertised” vs. “unadvertised” jobs. To do this, each job should be counted as active for every month that falls within its start and end date range.

What’s the best way to implement this logic?

sample data
job id ------ create date -------------- post date ------------ expired date
A ------ 2024-12-01 -------------- 2025-01-01 ----------- 2025-03-01

in the pivot table I want the advertised column to count :
advertised from post date to expired date and unadvertised from create date to post date?

2

Hello @prateek_san

I would recommend using extract() to pull out just the month part of both your date fields and date parameters.

Then you can use an ifelse() to compare the date field and your parameters on the month level and then count the job ids. Something like this:

ifelse(
   extract_month_create_date <= extract_month__param_start AND
   extract_month_expired_date <= extract_month_param_end,
   job_id,
   NULL
)

Then you can either run a distinct_count on that in the field well or nest the ifelse in a distinct count.

I don’t have a great dataset to test this on my end so if that breaks or doesn’t work let me know.

3

Hi @prateek_san

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 @prateek_san

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!