Forum Discussion

sam_detzler's avatar
sam_detzler
Cloud Apps
07-20-2022

Sporadic values for dates, want to see last score until next score is submitted

Hi all

I have a dataset containing numeric values for audit scores, a date field, and an auditee field. 

I can create a line chart with the score over time, however the data is "gappy" because the frequency of these audits is inconsistent. 

This presents a challenge, my customer would like to be able to see an average score for a filtered subset of the data (e.g. a group of organisations) at a specific date, but if I filter to the date (e.g. a one month period), it only shows me the audits from that month.

That average doesn't include any audit scores for organisations outside the month. 

So for example, if a score of "4" was bad, and if an audit had a score of 4 in June 2022 and I filtered to July 2022, then the score is not included in the average calculation for July 2022 even though the score of 4 is still valid for that organisation as it hasn't been updated.

Currently I'm thinking we could create a date table with a daily date field, which checks if the score has been updated and if not, record the last score. However this table will likely be several million rows long, and feels like overkill.

Any ideas?  

dashboards & reporting
data models

1 Reply

Replies have been turned off for this discussion
  • Silutions's avatar
    Silutions
    ETL
    07-21-2022

    Sam,

    Consider setting up your score calculation so that it includes the ALL() function for the date.  Your score calculation might look something like this:  AVG(score,ALL(Dates)).  This tells the calculation to ignore the date filter.  Note:  You have to account for each grain of Date, so if users can filter on years, quarters, weeks, days.  Your calculation will have to add an ALL for each.  Something like: 

    AVG(score, ALL(Years in Dates), ALL(Quarters in Dates), ALL(Months in Dates), ALL(Weeks in Dates), ALL(Days in Dates))

    Hope this addresses your use case, Jim