Reporting the negative
I have a business case to that requires me to report the locations or at worst the number of locations per region that have not had an audit in the past x number of weeks/months. My data set only includes the locations that have a started an audit. However, the entire location base had at least one successful audit in the calendar year 2023. The key fields in the data set are:
- locationName
- auditName
- region1Name
- auditScore
- auditPossibleScore
- startDate
- completeDate
The end user needs to be able to select the date range they are most interested in reviewing. I am trying to avoid using an ETL as my dataset is federated. How best could this be accomplished in a beast mode?
Best Answers
-
How about using something like GROUP_CONCAT to create a distinct list of all locations.
GROUP_CONCAT(DISTINCT locationName)
Create a calculated field that flags locations with audits within the date range desired.
CASE WHEN startDate >= [Start Date] AND startDate <= [End Date] THEN locationName ELSE NULL END
And filter out the locations with audits?** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
This isn't quite what you're asking for, but from a BeastMode, I think I'd do a date diff with the maximum completed date for a store and today's date. Then you could create viz for stores with the longest amount of time since their last audit, and filter for stores that have not had audit in at least __ days.
To do what you're describing, though, I think you'd need an ETL, because you need a row for every date that you want to be able to filter for in every store. With an ETL, you could do a full a join with your dataset and the calendar dataset. On that dataset, you could create a value for "days since last completed audit" for every day for every store. Then if you filter a date range, you'll be able to see which stores have the longest amount of time since the last audit.
Please 💡/💖/👍/😊 this post if you read it and found it helpful.
Please accept the answer if it solved your problem.
0
Answers
-
Duration of audits: DATEDIFF(completeDate, startDate)
Date Range: Apply a filter to your dataset to only include audits within the selected date range. Where [Start Date] and [End Date] as user set params.
CASE
WHEN startDate >= [Start Date] AND completeDate <= [End Date] THEN 1
ELSE 0
END
Identify locations without an audit:
IF(MAX(CASE WHEN [Date Range] = 1 THEN 1 ELSE 0 END, locationName) = 0, "No Audit", "")
Group by region and count the number within each region using something like: COUNT(DISTINCT CASE WHEN [No Audit Flag] = "No Audit" THEN locationName END)** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
That approach would capture cases were the audit was started and not completed. The issue is capturing the locations that have not had an audit started as I do not have a record for these locations. For example:
- Region 1 has 15 audits
- We know the list of stores assigned to region 1 because all 15 stores did receive an audit in 2023
- The auditor has completed 6 audits from 2024-03-01 to 2024-03-21
- They have started 4 additional audits in the same range
- There are no records for the 5 locations that have not had an audit started or completed
We want to be able to show in a visualization that there are 5 locations that have not had an audited started or completed. Ideally, we would show the list of those locations.
0 - Region 1 has 15 audits
-
How about using something like GROUP_CONCAT to create a distinct list of all locations.
GROUP_CONCAT(DISTINCT locationName)
Create a calculated field that flags locations with audits within the date range desired.
CASE WHEN startDate >= [Start Date] AND startDate <= [End Date] THEN locationName ELSE NULL END
And filter out the locations with audits?** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
This isn't quite what you're asking for, but from a BeastMode, I think I'd do a date diff with the maximum completed date for a store and today's date. Then you could create viz for stores with the longest amount of time since their last audit, and filter for stores that have not had audit in at least __ days.
To do what you're describing, though, I think you'd need an ETL, because you need a row for every date that you want to be able to filter for in every store. With an ETL, you could do a full a join with your dataset and the calendar dataset. On that dataset, you could create a value for "days since last completed audit" for every day for every store. Then if you filter a date range, you'll be able to see which stores have the longest amount of time since the last audit.
Please 💡/💖/👍/😊 this post if you read it and found it helpful.
Please accept the answer if it solved your problem.
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 709 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive