topic Re: RangeAvg(Above not working in a line chart in App Development

RangeAvg(Above not working in a line chart

ASC-Mike — Fri, 04 Oct 2024 15:51:45 GMT

Hello Qlik Community,

I can't seem to get the RangeAvg(Above working in a line chart, it is reporting lower numbers than expected and what is reported in a straight table. No problem using the RangeAvg(Above in a straight table.

The variable vMeasureSelector3 is made up of numerous master measures, one example is "OFD" which is this expression: Num(Count(if(StopTypeCode='D', StopNum)), '#,##0')

Here is the expression:

IF(
vAggregationType = 'Daily',
[$(vMeasureSelector3)],
IF(
vAggregationType = 'Rolling Average - 7',
RangeAvg(
Above([$(vMeasureSelector3)], 0, 7)
)
)
)

Thanks/Mike

Re: RangeAvg(Above not working in a line chart

rubenmarin — Sun, 06 Oct 2024 06:45:55 GMT

Hi, maybe adding a TOTAL:

IF(
vAggregationType = 'Daily',
[$(vMeasureSelector3)],
IF(
vAggregationType = 'Rolling Average - 7',
RangeAvg(
Above(TOTAL [$(vMeasureSelector3)], 0, 7)
)
)
)

If doesn't works, you can upload a sample app (with some dummy data in an inline table) that demonstrates the issue so we can take a look.

Re: RangeAvg(Above not working in a line chart

ASC-Mike — Sun, 06 Oct 2024 19:23:51 GMT

Hello @rubenmarin

I stripped down the entire app and left just what is needed, using the same data that is in the SQL tables for the OFD (Stops). App is attached.

This is what it should look like for the rolling 7, the first was output using python from the same data, the second is from a tableau app with same data. The first line should all be in the mid to upper 200s.

Thanks/Mike

Re: RangeAvg(Above not working in a line chart

rubenmarin — Mon, 07 Oct 2024 11:13:23 GMT

Hi, the runnign average could be esasily calculated usiong the expression modifers, as:

But I see there are days without data, should this days without data to be counted as 0 or just skipped from the average?

Note that when you use Above(...7) it will get the previous 7 values, which may not be the last 7 days as if there are skipped days, the last 7 values will go beyond those 7 days.

Having that in mind I would try to get the precalculated accumulated values in script, also the number of values used in that accumulation or a fixed '7' if skipped dates should count as zero.

Then the expression will be only the division between the accumulated and the number of days used for that date accumulation. this has many advantages, like performance ansd also you don't needd a chart with at least 7 valus to get the rolling avg, you can pick any date or arange of date and you will get the polling avg from the first point of data.

If there are many selections to by applied and the rolling avg should use them, maybe the solution is to use a similar scenario to an asofcalendar (https://community.qlik.com/t5/Design/The-As-Of-Table/ba-p/1466130) so each date can access the last 7 days data, allowing them to be calculated on execution time, without the need of precalculated accumulations.

Re: RangeAvg(Above not working in a line chart

Kushal_Chawda — Mon, 07 Oct 2024 12:30:39 GMT

@ASC-Mike try below expression

IF( vAggregationType = 'Daily', [$(vMeasureSelection)], IF( vAggregationType = 'Rolling Average - 7', sum(aggr(RangeAvg(Above([$(vMeasureSelection)], 0, 7)),[$(vDimensionSelection)],(Date,(NUMERIC)))) ) )

Re: RangeAvg(Above not working in a line chart

ASC-Mike — Mon, 07 Oct 2024 14:00:25 GMT

@Kushal_Chawda Thanks that worked. I similar variations using sum and aggr but clearly not as your solution. Thanks again.

Re: RangeAvg(Above not working in a line chart

Kushal_Chawda — Mon, 07 Oct 2024 14:02:46 GMT

@ASC-Mike Sorting date within Aggr is the key here.