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

Abhijith
Abhijith Member
edited March 2023 in Datasets

Input file

Domo1.docx


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

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • Abhijith
    Abhijith Member

    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

  • Abhijith
    Abhijith Member

    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
    GrantSmith Coach

    @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}]}


    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Abhijith
    Abhijith Member
    edited November 2021

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

    1.PNG


  • Abhijith
    Abhijith Member

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

    2.PNG

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

    1.PNG


  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    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.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • Abhijith
    Abhijith Member

    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.

Categories