How do you build a Single Value card when using a variable?
I am trying to produce a single value card that shows the number of active employees at a specific point in time. The data set columns included:
- employeeId
- employeeFirstName
- employeeLastName
- employeeStatus
- employeeStatusStartDate
- employeeStatusEndDate (this is null for the current status)
- locationName
- regionName
- rowFingerprint (unique identifier)
There is a beast mode calculation called ActiveUniqueUsers:COUNT(DISTINCT case when employeeStatuslike 'ACTIVE' then employeeId end)
An employee can have one of three possible status at any time and over time they can move between any of the 3 statuses.
I have created a date variable called StatusAsOf that allows the user to select a specific date. I use the variable in my beast mode calculation (filterHistorical):
CASE
WHEN StatusAsOf >= MAX(employeeStatusStartDate)
AND StatusAsOf <= MIN(IFNULL(employeeStatusEndDate,DATE_ADD(CURRENT_DATE(), INTERVAL 1 day))) THEN 'Include'
ELSE 'Exclude'
END
I have filterHistorical as a filter on the visualization set to value of "Include"
I have been able to verify that the the filterHistorical works by using a table visualization that contains the employeeStatusStartDate and employeeStatusEndDate
Best Answer
-
I might be overlooking a couple of things, but what you need your variable to do is to find the entry in your table where the value falls between the
employeeStatusStartDate
andemployeeStatusStartDate
, so you don't need aggregations, as with single value cards that will affect the whole dataset as you're not breaking it down to any level.Try using the following beast mode for the filter:
CASE WHEN `employeeStatusStartDate` <= `StatusAsOf` AND (`employeeStatusEndDate` IS NULL OR `employeeStatusEndDate` >= `StatusAsOf`)THEN 'Include' ELSE 'Exclude' END
Keep in mind this logic works as long as there are not 2 active statuses for the same employee at the same time. In the Mega Table this should yield the same result as the previous one but should also work for the single value card.
0
Answers
-
If you switch it to a table card, do you see employees? Otherwise the issue might be with your aggregations, I don't think you need them if each employee has those columns on them.
0 -
Yes, if I change it to table view I can get to the employees that have a max status start date less than the status as of variable date and the min end status end date greater than the status as of date variable.
For the single value visualization the setup I have the value set to ActiveUniqueUsers. For the optional label name I have tried:
- Null - returns no data
- employeeId
- employeeStatus - returns no data
- employeeStatusStartDate
- employeeStatusEndDate (this is null for the current status)
- locationName
- rowFingerprint (unique identifier)
None of them return the same results as I see in the table view which matches what is in the data set
0 -
I might be overlooking a couple of things, but what you need your variable to do is to find the entry in your table where the value falls between the
employeeStatusStartDate
andemployeeStatusStartDate
, so you don't need aggregations, as with single value cards that will affect the whole dataset as you're not breaking it down to any level.Try using the following beast mode for the filter:
CASE WHEN `employeeStatusStartDate` <= `StatusAsOf` AND (`employeeStatusEndDate` IS NULL OR `employeeStatusEndDate` >= `StatusAsOf`)THEN 'Include' ELSE 'Exclude' END
Keep in mind this logic works as long as there are not 2 active statuses for the same employee at the same time. In the Mega Table this should yield the same result as the previous one but should also work for the single value card.
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 299 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 55 App Studio
- 40 Variables
- 682 Automate
- 175 Apps
- 451 APIs & Domo Developer
- 46 Workflows
- 10 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive