Reporting the negative

D_Markley34

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?

ArborRose

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?

DavidChurchman

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.

ArborRose

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)

D_Markley34

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.

ArborRose

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?

DavidChurchman

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.