topic Re: 14 Day Rolling Average Percentage in QlikView

14 Day Rolling Average Percentage

rtr13 — Sat, 16 Nov 2024 18:16:52 GMT

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!

Re: 14 Day Rolling Average Percentage

jwjackso — Fri, 31 Jul 2020 18:46:27 GMT

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.

Re: 14 Day Rolling Average Percentage

rtr13 — Fri, 31 Jul 2020 19:05:00 GMT

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?

Re: 14 Day Rolling Average Percentage

jwjackso — Fri, 31 Jul 2020 19:09:31 GMT

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.

Re: 14 Day Rolling Average Percentage

jwjackso — Fri, 31 Jul 2020 19:13:55 GMT

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

Re: 14 Day Rolling Average Percentage

rtr13 — Fri, 31 Jul 2020 19:21:04 GMT

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?

Re: 14 Day Rolling Average Percentage

jwjackso — Fri, 31 Jul 2020 19:29:12 GMT

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;