Turnover calculation query

Abhijith
Abhijith Member
in Magic ETL

The assistance from the community was great and I have been exploring Domo. This request is in continuation with this data set https://dojo.domo.com/discussion/comment/55564#Comment_55564

Copy of TURNOVER FORMULA.xlsx

Based on the turnover calculation in the excel file, I would like to get some guidance on whether it can be done on the beast mode or does it require another ETL process. I would really appreciate how I could incorporate these excel formulas into Domo for every month's turnover calculation to display the using the 24 months data. Thanks in advance.

Answers

  • Abhijith
    Abhijith Member
    edited November 2021

    Hi @amehdad ,

    Thanks for the write-ups from @GrantSmith

    turnover1.docx

    For some reason, I'm not able to view the cumulative effect as per the windows function. My intention is to get the grand total of all the 'Terminated' for these months so that I can use them in the turnover formula. Please advise if I'm going in the right direction.

  • Abhijith
    Abhijith Member
    edited November 2021

    Hi @amehdad ,

    Thanks for the write-ups from @GrantSmith

    Based on that I tried to work it out and I'm not winning.

    turnover1.docx

    My basic objective is to get the total sum of 'terminated' for that period(it is always for 12 months) so that I can use it in my calculation.

    Kindly advise what might be wrong. Thanks

  • Abhijith
    Abhijith Member

    Hi @amehdad ,

    Let me check and revert back. Thanks

  • Abhijith
    Abhijith Member

    @amehdad

    I'm still waiting on the feedback from Domo support. They have asked me to wait. I'll revert back to you once I received confirmation from their end. Thanks

  • Abhijith
    Abhijith Member

    Hi @amehdad

    They have now confirmed that the windows function has been enabled. Can you please check and advise how do I take it forward. Thanks once again.

  • Abhijith
    Abhijith Member

    I needed direction on how to work out the formula for the calculation in the excel file attached at the beginning of the thread. Especially how do I identify the average of the first and last month for each month's calculation.

  • Abhijith
    Abhijith Member

    Hi @amehdad

    I got those calculated fields for 'Exit' and 'Employed' but yeah I was stuck at the part where to generate dyamically.

    Also is there any way I can sum up all the 'exits' for a period(say 12 months fixed) and then I can use it to work out the turnover rate calculation.?

  • GrantSmith
    GrantSmith Coach
    edited December 2021

    @Abhijith

    You should be able to use multiple lag functions to calculate the total and average. This is assuming your data has been unpivoted and you have a single row for each month instead of a single column for each month as in your excel document.

    Part 1:

    (LAG(SUM(`Exits in month`), 12) OVER (ORDER BY `headcount as at date`) +
(LAG(SUM(`Exits in month`), 11) OVER (ORDER BY `headcount as at date`) +
(LAG(SUM(`Exits in month`), 10) OVER (ORDER BY `headcount as at date`) +
(LAG(SUM(`Exits in month`), 9) OVER (ORDER BY `headcount as at date`) +
(LAG(SUM(`Exits in month`), 8) OVER (ORDER BY `headcount as at date`) +
(LAG(SUM(`Exits in month`), 7) OVER (ORDER BY `headcount as at date`) +
(LAG(SUM(`Exits in month`), 6) OVER (ORDER BY `headcount as at date`) +
(LAG(SUM(`Exits in month`), 5) OVER (ORDER BY `headcount as at date`) +
(LAG(SUM(`Exits in month`), 4) OVER (ORDER BY `headcount as at date`) +
(LAG(SUM(`Exits in month`), 3) OVER (ORDER BY `headcount as at date`) +
(LAG(SUM(`Exits in month`), 2) OVER (ORDER BY `headcount as at date`) +
(LAG(SUM(`Exits in month`), 1) OVER (ORDER BY `headcount as at date`)

    Part 2:

    (LAG(SUM(`Employed`), 12) OVER (ORDER BY `headcount as at date`) + SUM(`Employed`)) / 2


    Turnover Rate:

    (
 (LAG(SUM(`Exits in month`), 12) OVER (ORDER BY `headcount as at date`) +
 (LAG(SUM(`Exits in month`), 11) OVER (ORDER BY `headcount as at date`) +
 (LAG(SUM(`Exits in month`), 10) OVER (ORDER BY `headcount as at date`) +
 (LAG(SUM(`Exits in month`), 9) OVER (ORDER BY `headcount as at date`) +
 (LAG(SUM(`Exits in month`), 8) OVER (ORDER BY `headcount as at date`) +
 (LAG(SUM(`Exits in month`), 7) OVER (ORDER BY `headcount as at date`) +
 (LAG(SUM(`Exits in month`), 6) OVER (ORDER BY `headcount as at date`) +
 (LAG(SUM(`Exits in month`), 5) OVER (ORDER BY `headcount as at date`) +
 (LAG(SUM(`Exits in month`), 4) OVER (ORDER BY `headcount as at date`) +
 (LAG(SUM(`Exits in month`), 3) OVER (ORDER BY `headcount as at date`) +
 (LAG(SUM(`Exits in month`), 2) OVER (ORDER BY `headcount as at date`) +
 (LAG(SUM(`Exits in month`), 1) OVER (ORDER BY `headcount as at date`)
)
/
(
  (LAG(SUM(`Employed`), 12) OVER (ORDER BY `headcount as at date`) + SUM(`Employed`)) / 2
)


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

    Hi @GrantSmith

    Thanks for the suggestion. Based on your feedback I managed to find the turnover % for a month. But now if I want to find the turnover% for the other months, is it possible to do that in the same beast mode or do I need to create a separate beast mode?

    image.png


    I'm using the bogus dataset shared earlier.

    {"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}]}


  • GrantSmith
    GrantSmith Coach

    If you want the 12 month turnover rate you'll need more than 1 year worth of data. When it's attempting to do the lag for prior months and getting 12 prior records it's returning NULL. When you use + and NULL the end result is a NULL so your data is blank for all of the prior months. The beast mode should work assuming you have more than 12 months of data in your dataset.

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

Categories