topic Re: rolling 7-day average in App Development

rolling 7-day average

daveatkins — Mon, 20 Dec 2021 21:08:46 GMT

I need an expression that will allow plotting a line graph of the rolling 7 day average of a calculation.

First to get the numerator, I have tried this

Count({<Date={">=$(date(floor(Date))-7)<$(date(floor(Date)+1))"}>} RESULT)

but it does not work; returns the same as count(RESULT)

The measure I am graphing is a ratio; i.e.

7 day average positivity = count(where RESULT='POS') / count(RESULT)

and I need to know what to add to change that from a daily average to a rolling 7-day average with the x-axis dimension of Date

Re: rolling 7-day average

chrismarlow — Thu, 12 Nov 2020 20:24:30 GMT

Hi,

You would use Rangesum & above similiar to this post;

https://community.qlik.com/t5/New-to-Qlik-Sense/Rolling-7-days/td-p/114610

Cheers,

Chris.

Re: rolling 7-day average

daveatkins — Thu, 12 Nov 2020 23:06:08 GMT

I came up with a formula, but it does not work across additional dimensions. For example, I have a column for "region" and want line plots for each region. The expression doesn't allow for splitting out the additional dimension:

This is the rolling 7-day count of tests performed. (this just the denominator in the positivity average I ultimately need).

sum(aggr(rangesum(above(total count({<TestingDate=>}if(not isnull(VerifyDate),AcctNum)),0,7)),TestingDate))

oh...read the documentation...

sum(aggr(rangesum(above(total count({<TestingDate=>}if(not isnull(VerifyDate),AcctNum)),0,7)),TestingDate, Region))

I think that solves it!