rolling 7-day average

[daveatkins]

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

[chrismarlow]

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.

[daveatkins]

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!