Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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!