14 Day Rolling Average Percentage

[rtr13]

Hi Everyone,

I am currently trying to create combo chart that includes a bar graph for total tests and the line  would be for a 14 day rolling average % for patients that test positive to the certain test. I am having problems putting together the 14 day rolling average %. 

Some of the fields that I have include the following: OrderDate, Test, and CustomerID. Each point should take into consideration the past 14 dates and create an average percentage for tests that came back positive. Unfortunatly I don't have any test data, but can help clarify anything if needed.

Thanks in advance!

[jwjackso · Accepted solution]

I do something similar using Henric's As-Of-Table 

The dimension for the chart is the AsOfDate.

The Bar expression is Count({$<DayDiff={0}>} Patient), the DayDiff={0} limits the count to individual days

The Line expression is 

=Count({1<DayDiff={"<14"}>}Patient)
/ Count({$<DayDiff={"<14"},Date={">=$(=Min(Aggr(Only({$<DayDiff={0}}>}Date),Date,Date)))"}>}Distinct Date)

The DayDiff={"<14"} counts the patients for the last 14 days

The Date={">=$(=Min(Aggr(Only({$<DayDiff={0}}>}Date),Date,Date)))"} is to identify the earliest date with data.  This is needed because when you are at the beginning of the data set, you want to divide the numerator with the number of days that had data.  For most of the days is will be 14, but early on it will range from 14 to 1.

[rtr13]

This is helpful! A question I have is when coming up with the DayDiff variable, within the AsOfTable, will I replace Month with Date to create the DayDiff variable? 

[jwjackso]

You can replace the MonthDiff or just add the DayDiff.  It depends if you would be doing any Rolling Month calculations as well as Rolling Days.

[jwjackso]

Just to be clear, you would replace Month with Date.  But like in the original you could have a DayDiff, MonthDiff and YearDiff if they were helpful

[rtr13]

This is perfect. When it comes to adding in DayDiff within the AsOfTable, how would this be calculated? Within the AsOfTable when calculating MonthDiff it uses the following:   Round((AsOfMonth-Month)*12/365.2425) as MonthDiff. Would this be replaced with   Round((AsOfMonth-Month)/365.2425) as DayDiff to accurately represent DayDiff?

[jwjackso]

In our case, we are  only looking at most a Rolling 30 Day window.  The DayDiff is just AsOfDate - Date.  The section in bold below limits the DayDiff  from 0 to 30.

tmpAsOfDate:
Load Distinct Date
Resident MasterCalendar
Where Date >= Date('03/01/2020','MM/DD/YYYY');
join (tmpAsOfDate)
Load Date as AsOfDate
Resident tmpAsOfDate;

AsOfDate:
Load Date,
AsOfDate,
AsOfDate - Date as DayDiff;
Load Date,
AsOfDate
Resident tmpAsOfDate
where AsOfDate >= Date and AsOfDate <= Date + 30;

Drop Table tmpAsOfDate;