How to build card|total headcount based on the Hire date and termination date

Abhijith · 2021-10-29T02:00:07+00:00

There was an error rendering this rich post.

Input file

Hi,

I am new to Domo and have been asked to build a card that displays the total headcount for the year to date based on the Hire date and termination date. However, this selection should be used to check for the last two years as well.

1. What is expected is to show the total active employees for that month for that period as displayed here.

2. Then based on that, the total turnover% per month for employees with < 1-year service and employees with > 1-year service to be worked out.

Guidance on whether this could be done through the ETL or beast mode and how to achieve this would be really appreciated.

Thanks

Find more posts tagged with

Accepted answers

GrantSmith

dt was the date field I was using. You’ll need to tweak the ETL to replace dt with your hire date field and term_dt with your termination date field.

All comments

amehdad

Hi @Abhijith, for your first Q, as a start, this is an option to get total employees active:

COUNT(

CASE

WHEN status = 'A'

AND 'Hire Date' <= DATE_FORMAT(CURRENT_DATE(),'%d/%m/%Y')

AND 'Termination Date' IS NULL

THEN 'ID Number'

ELSE 0

END)

You could use 'COUNT(DISTINCT ... ' in the front instead.

To get the graph view by month, an option is to bring in another date field as a check of activeness (such as a 'login date' field or similar). This is where ETL comes in, a dataset that has a daily log of employees, that you can join by ID number.

Abhijith

Hi @amehdad ,

Thanks for your response. But in this case, with the first Q, this will only show the total hired at that month. For example, it will not show in the below format, wherein it summarises the total for that month i.e. provide a total number of employees working during that month.

SEP OCT NOV DEC

440 420 430 450

amehdad

Yep so as the beast mode above uses your two dates, you'll need a separate date field to create that month by month view. You can either use another date field in your 'Input' dataset (for example, dataset updated date), or can bring in a date field (like a daily employee log) from another dataset, via ETL and joining by ID Number, into your working one.

Abhijith

Hi @amehdad ,

I was just exploring and then based on some hints, I managed to introduce a date dataset into the ETL and then ran into some other issues which I am sorting out. I do not have any other ETL which I could use it join based on ID. I will revert back to you soon.

However I came across this (How Many Staff Do We Currently Have - Multiple Dates Logic In Power BI Using DAX | Enterprise DNA ) in Power BI and I think I'm trying to replicate a similar approach in Domo. Kindly let me know your thoughts on it. Thanks.

GrantSmith

@amehdad

Your beast mode won't work correctly as count(0) is still 1. You'd need to drop your else clause if you're using count (how many non-null values are there?)

COUNT(

CASE

WHEN status = 'A'

AND 'Hire Date' <= DATE_FORMAT(CURRENT_DATE(),'%d/%m/%Y')

AND 'Termination Date' IS NULL

THEN 'ID Number'
END)

@Abhijith Here's a rough start to how you can do this in Magic ETL 2.0 where it's calculating how many were hired, terminated and the total for each month. At a high level it's using the date dimension from the Domo Dimensions connector and then filtering based on when the employee was hired and fired. Hire date is dt and termination date is term_dt I just generated a bogus dataset for testing. You can copy and paste this code into the Magic ETL

{"contentType":"domo/dataflow-actions","data":[{"name":"Domo Dimensions - Calendar","id":"07583568-71c1-4868-b904-f6dda0f4e1c4","type":"LoadFromVault","gui":{"x":192,"y":384,"color":3238043,"colorSource":null},"dependsOn":[],"removeByDefault":false,"notes":[],"dataSourceId":"d6b99a99-63c7-46f0-8e54-38045e29e710","executeFlowWhenUpdated":false,"onlyLoadNewVersions":false,"columnSettings":{},"visiblePartitionColumn":null},{"name":"Select Columns","id":"4a46d8f5-9cd3-4fa8-91de-cfa071971e63","type":"SelectValues","gui":{"x":300,"y":384,"color":null,"colorSource":null},"dependsOn":["07583568-71c1-4868-b904-f6dda0f4e1c4"],"removeByDefault":false,"notes":[],"fields":[{"name":"dt","rename":null},{"name":"d","rename":null}]},{"name":"Add Formula","id":"f01f1bab-c817-4fbc-a0ab-7e657ce6aadf","type":"ExpressionEvaluator","gui":{"x":408,"y":384,"color":null,"colorSource":null},"dependsOn":["4a46d8f5-9cd3-4fa8-91de-cfa071971e63"],"removeByDefault":false,"notes":[{"x1":null,"y1":null,"x2":null,"y2":null,"body":"Calculating the first day and last day of the month to make monthly totals easier to calculate."}],"expressions":[{"expression":"LAST_DAY(`dt`)","fieldName":"Last Day of Month","settings":null},{"expression":"DATE_SUB(`dt`, INTERVAL (DAYOFMONTH(`dt`) - 1) DAY)","fieldName":"First Day of Month","settings":null}]},{"name":"Add Constants","id":"663b8cde-daf5-4879-bece-0808c21f5ac1","type":"Constant","gui":{"x":492,"y":264,"color":null,"colorSource":null},"dependsOn":["7468a0b2-7cc8-46ef-9fc7-6c12ec70a05b"],"removeByDefault":false,"notes":[{"x1":null,"y1":null,"x2":null,"y2":null,"body":"Adding a constant to do a cross join on\n"}],"fields":[{"name":"Join Column","type":"LONG","expr":null,"value":"1"}]},{"name":"Get Unique Months","id":"108e1583-348c-49fa-a485-20ad10442bee","type":"GroupBy","gui":{"x":504,"y":384,"color":null,"colorSource":null},"dependsOn":["f01f1bab-c817-4fbc-a0ab-7e657ce6aadf"],"removeByDefault":false,"notes":[{"x1":null,"y1":null,"x2":null,"y2":null,"body":"Grouping on the first day and last day of the months to have less data to join to and improve efficiency. There's a little hackery going on here with the join column as the min(d) will always be 1 and we'll use that to do a cross join."}],"addLineNumber":false,"lineNumberFieldname":null,"giveBackRow":false,"allRows":false,"groups":[{"name":"Last Day of Month"},{"name":"First Day of Month"}],"partitionedAggregation":false,"fields":[{"name":"Join Column","source":"d","type":"MIN","valuefield":null}]},{"name":"Join Data","id":"8e6dbe75-36a9-404e-9a38-58a905ddd17d","type":"MergeJoin","gui":{"x":612,"y":312,"color":null,"colorSource":null},"dependsOn":["663b8cde-daf5-4879-bece-0808c21f5ac1","108e1583-348c-49fa-a485-20ad10442bee"],"removeByDefault":false,"notes":[{"x1":null,"y1":null,"x2":null,"y2":null,"body":"Doing a giant cross join since Domo doesn't support anything other than key value joins in Magic ETL 2.0."}],"joinType":"INNER","step1":"663b8cde-daf5-4879-bece-0808c21f5ac1","step2":"108e1583-348c-49fa-a485-20ad10442bee","keys1":["Join Column"],"keys2":["Join Column"],"schemaModification1":[{"name":"Join Column","rename":"","remove":true}],"schemaModification2":[{"name":"Join Column","rename":"","remove":true}],"partitioningInputId":""},{"name":"Filter Rows","id":"0217d575-560b-4b1b-9c67-66e89730034d","type":"Filter","gui":{"x":720,"y":312,"color":null,"colorSource":null},"dependsOn":["8e6dbe75-36a9-404e-9a38-58a905ddd17d"],"removeByDefault":false,"notes":[{"x1":null,"y1":null,"x2":null,"y2":null,"body":"`dt` <= `Last Day of Month` - Was this person hired before the months end?\n\n AND (`term_dt` <= `Last Day of Month` The employee was terminated prior to the end of this month\nOR `term_dt` IS NULL) -- OR this person still employed"}],"filterList":[{"expression":"`dt` <= `Last Day of Month` AND (`term_dt` >= `First Day of Month` OR `term_dt` IS NULL)","andFilterList":[]}]},{"name":"Add Formula 2","id":"18c0c25d-ab75-48b2-8ad0-44893cc1996f","type":"ExpressionEvaluator","gui":{"x":828,"y":312,"color":null,"colorSource":null},"dependsOn":["0217d575-560b-4b1b-9c67-66e89730034d"],"removeByDefault":false,"notes":[{"x1":null,"y1":null,"x2":null,"y2":null,"body":"Calculate how many were hired in a month, terminated in a given month and how many were currently employed in the given month?\n\nTerminated is set to be -1 for display purposes so you can have a diverging bar chart each month"}],"expressions":[{"expression":"CASE WHEN LAST_DAY(`dt`) = `Last Day of Month` THEN 1 ELSE 0 END ","fieldName":"Hired","settings":null},{"expression":"CASE WHEN LAST_DAY(`term_dt`) = `Last Day of Month` THEN -1 ELSE 0 END","fieldName":"Terminated","settings":null},{"expression":"CASE WHEN (`term_dt` IS NULL OR `term_dt` > `Last Day of Month`) AND `dt` <= `Last Day of Month` THEN 1 ELSE 0 END","fieldName":"Employed","settings":null},{"expression":"DATEDIFF(`term_dt`, `dt`)","fieldName":"Employment Duration","settings":null},{"expression":"CASE WHEN `Employment Duration` < 365 THEN '< 1 Year' ELSE '1+ Years' END","fieldName":"Employment Duration Bucket","settings":null}]},{"name":"Select Columns 1","id":"f27dd286-f26b-4343-821f-3071c3df207d","type":"SelectValues","gui":{"x":936,"y":312,"color":null,"colorSource":null},"dependsOn":["18c0c25d-ab75-48b2-8ad0-44893cc1996f"],"removeByDefault":false,"notes":[],"fields":[{"name":"random_number","rename":null},{"name":"dt","rename":null},{"name":"term_dt","rename":null},{"name":"First Day of Month","rename":null},{"name":"Last Day of Month","rename":null},{"name":"Hired","rename":null},{"name":"Terminated","rename":null},{"name":"Employed","rename":null},{"name":"Employment Duration","rename":null},{"name":"Employment Duration Bucket","rename":null}]},{"name":"Employment Status","id":"0d8d4d76-8844-4a30-ac74-e905ac2cc70b","type":"PublishToVault","gui":{"x":1032,"y":312,"color":null,"colorSource":null},"dependsOn":["f27dd286-f26b-4343-821f-3071c3df207d"],"removeByDefault":false,"notes":[],"dataSource":{"guid":"34526147-5582-4cf6-abd9-27d45a364585","type":"DataFlow","name":"Employment Status","description":"https://dojo.domo.com/discussion/53740/how-to-build-card-total-headcount-based-on-the-hire-date-and-termination-date","cloudId":"domo"},"versionChainType":"REPLACE","partitionIdColumns":[]},{"name":"Input DataSet","id":"7468a0b2-7cc8-46ef-9fc7-6c12ec70a05b","type":"LoadFromVault","gui":{"x":372,"y":264,"color":10827556},"dependsOn":[],"removeByDefault":false,"notes":[],"executeFlowWhenUpdated":false,"onlyLoadNewVersions":false,"columnSettings":{},"visiblePartitionColumn":null}]}

Abhijith

@GrantSmith

Thanks for your message. A quick query, when you mentioned I could copy and paste into the Magic ETL, did you mention the below as I noticed it is more of a drag and drop. Or perhaps is there another option. Kindly advise.

amehdad

Hi @Abhijith, so when you have your empty canvas in the ETL, like the example below, you can then copy and paste Grant's code and it will automatically populate the tiles and dataflow.

Abhijith

@amehdad : Great thanks mate, that worked

@GrantSmith : So I began unpacking and noticed this strange bit. The column 'dt' gets identified in Filter rows but not after that.

In the 'Add formula 2', the 'dt' column is not getting detected. Is there anything I could check it? Kindly advise. Thanks

GrantSmith

dt was the date field I was using. You’ll need to tweak the ETL to replace dt with your hire date field and term_dt with your termination date field.

jaeW_at_Onyx

amendment i would make to @GrantSmith 's ETL.

if the employee has not been terminated, then sent the termination date to 1 year from today's date. that way you have a clean FILTER instead of filter on Term Date is NULL. also by setting a rule, you can include it in the data dictionary for how your dataflow / data set works.

consider adding binary columns isHire and isTerminate for the months when the employee was hired and terminated and / or isHeadcount so you have clean summable columns instead of working on the COUNT of something.

Abhijith

Hi @GrantSmith : I thought so but I got confused with the 'dt' column from the Domo calendar. Let me check it out and revert.

Hi @jaeW_at_Onyx : Thanks for your valuable input, it is really appreciated. I'll explore it further.