Calculating Percentage based on multiple dates; calculations based on date selection

user03841

Is there a function or variable(s) that provide the value of the selected date range (i.e. the “from” and “to” of the DOMO Between fields?

 

I would like to select employees who were active during a selected date range.  If, for example, the date range was Oct. 13, 2017 - Jan 7, 2018, then all employees who are not terminated (TermDate) before Oct 13, 2017 and who were employed by Jan 7, 2018 (HireDate) would be selected.

 

I would also like to select employees who were active during that period and who were also participating (EnrollDate and DisenrollDate) in a program during this period.

Darius

Hello, user03841,

 

You could use Aggregation functions in a Beast Mode to select the earliest and latest dates in your data that are in the date range you are filtering to. For example, you could do something like:

 

CASE

WHEN `TermDate` <= MIN(`your_date_field`) THEN 'Exclude'

WHEN `HireDate` >= MAX(`your_date_field`) THEN 'Exclude'

ELSE 'Include'

END

 

You could then use the above as a filter, keeping all rows that return "Include". You could use a similar syntax with the EnrollDate and Disenroll Date columns in a Beast Mode. Let us know if that helps!

PodiumMason

Hey @user03841,

 

You could do this with a beast mode (or multiple beast modes) and either hard code the date ranges you want to look at, or use the card date range to adjust the beast mode dynamically. (since you're using two different date columns, that probably wouldn't work thinking about it)

 

In your example you could do a beast mode like this:

 

Case when DATE(`TermDate`) > '2017-10-13' and DATE(`HireDate`) < '2018-01-07' and Date(`EnrollDate`) IS NOT NULL then `EmployeeId` END

 

This would give you employees that were at least working during that time frame and had enrolled at some point in the program. If you wanted to see those who enrolled and disenrolled during that time, you could add AND DATE(`DisenrollDate`) IS NOT NULL

 

Let me know if you have additional questions. 

 

Best of luck!

 

 

user03841

I will not know ahead of time what the selection begin date and end date will be.  I am looking at a range of dates (from start date to end date). I would like to use the DOMO "Between" start and stop dates.   

 

Is there a way to do this?  What is the `your_date_field` for the DOMO Between 'Start Date' and 'End Date' fields? 

 

Darius

Using the MAX and MIN functions, you will be using the earliest and latest dates available in your data rather than predetermined dates. By `your_date_field`, I am referring to the same date field that you are using in the date range grain (the Between dates) in your card. Please let me know if there is more that I can clarify about my response!

user03841

I can't hardcode the start date and end date values since the dates will vary. I am creating the card for individuals who will not have the knowledge of editing DOMO cards.

user03841

The dataset contains two date fields:  HireDate and TermDate.  I created a beast mode calculation against the Hiredate field and tried to filter on this field but DOMO won't allow it.  Says "An Issue has occurred during processing.  We are unable to comlete the request at this time."

user03841

Domo reports that "Aggregations are not supported in filters."

PodiumMason

@user03841,

 

If you want to see data only based on dates, have you tried using dates as filters on the card vs. creating a beast mode? 

 

You could put multiple date filters on the card (and potentially use them as quick filters, disclaimer, I haven't tried this) and without using a beast mode at all potentially get the data you need. 

 

Also, @Darius I don't think your first beast mode will work the way you intend, because the MAX dates on the inside of a beast mode would be pulling the max date for that LINE Edit: ROW not the entire DATASET. 

Darius

Sorry for the confusion, user03841. My answers assume that you are applying a date grain to your card, which is how I understood you when you said "the 'from' and 'to' of the DOMO Between fields". If you have applied a date grain to the card (Last 6 months, Previous Quarter, This year, etc), you can use the MAX and MIN functions, as mentioned. Please let me know if that makes sense, otherwise, I would be happy to answer any specific questions or clarify as needed.

Darius

@PodiumMason, you bring up a good point. The aggregations would be limited by the groupings for each row in the card.

 

One potential solution would be to generate columns for the beginning and end of your date range using a dataflow.  These columns would have the same values for every row in the dataset and could be generated dynamically on a daily basis in the dataflow. Looking at XervaMason's formatting, for example, '2018-01-07'  and '2017-10-13' could be replaced with these columns, which would include the dates for your shifting date range. Otherwise, the recommendation to use multiple date filters is a good approach to try as long as it does not limit your data too much and does not exclude results you are expecting to see.

user03841

I am using hiredate in the date range grain field and am still having trouble with using the beast mode calculation in the filter.

 

If I use hiredate in the date range grain field, only employees hired during that range will be selected.  I am looking for all Active employees during that time.

 

If I select Oct 13, 2017 as the selection start date but the earliest hire date that is on or after Oct 13, 2017 is Oct 23, 2017 would this beast mode calculation miss someone who has a termdate of  Oct 20, 2017?

 

I envision a user of this card using it to display the active employees for multiple date ranges.  Is it possible to dynamically create the "Active Employee" field in a dataflow using the date range input on the DOMO card?

user03841

@PodiumMason :

If I select on HireDate, only Employees hired during that time are selected.  If TermDate is used, then employees terminated during that date range will be selected.

 

The users of this card will probably try different combinations of start and stop dates so the calculation of active employees needs to be dynamic.

Darius

@user03841, 

 

Based on your use case it sounds like a dataflow would be the best way to establish a reliable definition of an active employee. To retain as much data as possible, I would suggest generating a value that acts as a flag to identify users as active or inactive. This would allow you to leave all of the data intact while allowing you to filter within cards to only see active employees. Is that an acceptable approach to you for moving forward? If so, what questions do you have about that approach so we can help you design a roadmap?

user03841

We already have a flag for employees who are currently active:  it checks if the termdate is active.  How would you create a dataflow that would accept selection stop dates and start dates from a DOMO card to compare that with both the Hire dates and End dates?   I can create the SQL statement to compare the hire and term dates.  I don't know how to pass the values of the user input begin and end dates .

user03841

I would like to create a dataflow that would determine if an employee is active based on start and stop dates that can change.  For example,  it should be able to count the number of employees active between '01/12/2014' and '03/15/2016' as well as between '4/6/2017' and '7/7/2017'.

[Deleted User]

@Darius @PodiumMason

 

Any additional thoughts here?

Thanks!

user03841

When I tried to use the beast mode you suggested with MAX and MIN in a filter, DOMO would not allow it to be used as a filter. The message was: "Aggregations are not supported in filters."

 

Using HireDate as the Date range field, and '2017-08-01' through '2017-10-15' as the date filter range, would a DOMO card pick up an employee who was terminated on Aug 4, 2017 if the earliest hiredate within that range was Aug 7, 2017?

user03841

Thanks for the reply.  I was looking to not hardcode the date selection since others will be using this card for multiple date ranges.

user03841

We are not applying a date grain to the card since users of this card may wish to enter any start date and stop date. 

user03841

Do you identify an employee as Active or Inactive if the start and stop dates can change?  In other words, how do you identify the Date Range field Start Date and Stop Date in the dataflow?

user03841

How would you create a dataflow to identify employees who were active in a previous time period, where the time period is input by the DOMO card User?  This could include employees who are currently active.

[Deleted User]

@Darius @PodiumMason

 

Any additional thoughts?

Thanks!

Darius

@user03841,

 

Sorry for the delay. You asked how you would identify the Date Range start and stop dates for active/inactive classification. This would depend on your definition of that time frame. Is it the last ____ months? If not, how do you define the active range?

 

Overall, it sounds like you are working with a highly customized request, so my recommendation would be to reach out to your Customer Success Manager to look into solutions that we can provide. Otherwise, if you have general questions, I would be happy to do my best to point you in the right direction.

user03841

The time period would be whatever the user inputs in the Date Range Between Start Date and End Date fields on the DOMO card (e.g. 10/6/2016 - 11/18/2016;  08/08/2017 -  3/5/2018; 12/10/2017 - 12/20/2017) .  It wouldn't be limited to a current or previous week, month, quarter or year. 

Darius

Thank you for that information. I see that you are in communication with our Support team. The recommendation there was that you prepare a row for each user so you can have a dynamic range on a per-user basis. The best way to do that is with a dataflow that joins the start and end dates onto the username, creating one row per user. 

user03841

The users may want to query on multiple start and stop dates in a session.  Do you mean DOMO Card user or Employees that are contained in the dataset?