possible to have 2 date ranges?

meadow_ryan

I have a card I'm trying to build where there is a hire_date for an employee and a termination_date for an employee, where we are trying to determine the retention which would be the number of employees who left divided by the number hired. Problem I'm coming across is how to have two different dates on the card to

 

1) display these rows of employes

 

2) display the percentage as a summary number

ST_Superman

You could use a beastmode to filter a date range:

 

CASE WHEN `hire_date`<1/1/2018 and `hire_date`>12/31/2016 then 'true' else 'false' end

 

You can then use this as a filter to show the employees that were hired in 2017 by putting this beastmode in a filter and selecting 'true'

 

For the second date range, you can either create another beastmode, or if you want to have the ability to change the range you would set the date range as `termination_date`

BlueRooster

Something we did so that you could leave the functionality of the date filter for the user was to select all values for each date field and union them while creating 2 new columns, UnifiedDate and DateType.

 

It might look something like this for you:

SELECT *,
              `hire_date` AS 'Unified_Date',
              'Hired' AS 'DateType'
FROM dataset
WHERE `hire_date` IS NOT NULL

UNION ALL

SELECT *,
              `termination_date` AS 'Unified_Date',
              'terminated' AS 'DateType'
FROM dataset
WHERE `termination_date` IS NOT NULL

The result is that for every metric you define at the card level you can define a CASE WHEN.

Yours would then look like this:

/* For Hired Employees */
CASE WHEN `DateType` = 'Hired' THEN `EmployeeName' END

/* For Term'd Employees */
CASE WHEN `DateType` = 'Terminated' THEN `EmployeeName' END

So you could create a table card with 3 columns, Unified Date, the Hired Employees BeastMode and the Term'd Employees BeastMode.

And finally summary number is just the following:

SUM(CASE WHEN `DateType` = 'Hired' THEN 1 END)
/
SUM(CASE WHEN `DateType` = 'Terminated' THEN 1 END)

And that way it would all be dynamic with whatever date range the user selects.

 

Hope that helps,

ValiantSpur

 

**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.

meadow_ryan

by display the percentage as a summary number meaning the rentention percentage....Is there a way to allow the dates to roll so we manually don't have to change the dates each month?

BlueRooster

If you follow what I mentioned above and you can set the date filter to whatever timeframe you want and it will roll with the dates as they change (ie, Last 6 Months).

 

Let me know if you need more detail

meadow_ryan

so I've buildt the card but nothing is showing up? Also for the summary date what do I put in for the date range field?

 

https://meadowlarkco.domo.com/page/1446302714/analyzer?cardid=2028803795

 

 

I'm not sure if you can see from my link

ST_Superman

@Valiant - If I'm following the logic for your suggestions, I'm not sure that it's going to provide the results that he is looking for.  If you apply a filter for a date range, lets say Q1 2017, then the data set that you are suggesting will return only the number of employees with a hire date or a termination date that falls in that quarter.  I do not think it will provide a total number of employees working during that time.  

 

Unfortunately, I'm not sure that I have a solution that will provide the desired results.  I would probably look to create a data set that would update each month with the previous month's data.  I would include a column for year, quarter, month, (any categorical fields you want to slice by: i.e. region, district, manager, etc.), location, active employees(employee id's), terminated employees (employee id's).  Then you could set up a recursive data flow that would append each month's data onto a new data set that you could title historical retention rates.  

 

This would then allow you to set a date range by filtering for specific years/quarters/months and you could set a beastmode to count(distinct(`active employees`)) which would give you the number of employees that were active at that location or region during those months.  count(distinct(`terminated employees`)) would give you the number of employees terminated during the time period.  Retention rate would be sum(count(distinct(`terminated employees))) / sum(count(distinct(`active employees`)))

meadow_ryan

ultimately we want to find the retention ( number of employees left / number hired) and then list the number of employees for each plus be able to change that each month for the hired and termination date