topic RangeAvg(Above( Aggr( not working in line chart with date range in App Development

RangeAvg(Above( Aggr( not working in line chart with date range

ASC-Mike — Sat, 19 Oct 2024 17:26:01 GMT

Hello Qlik Community,

I can't seem to get the RangeAvg(Above working in a line chart, it is producing a blank chart with the expression below:

Here is the expression:

// Rolling Average - 7 Calculation for Prior Period with Proper Date Handling
IF(vComparisonPeriod = 'Prior Period',
Num(RangeAvg(Above(Aggr(

IF(StopDate >= Date(Min([StopDate]) - (Max([StopDate]) - Min([StopDate]) + 1), 'M/D/YYYY') AND StopDate <= Date(Min([StopDate]) - 1, 'M/D/YYYY'), [$(vMeasureSelector3)]),
[$(vDimensionSelector)], StopDate.autoCalendar.Date),1, 7)),$(vMeasureSelector3Format) // Apply the formatting
))

I do have it working with the filtered dates using this expression:

IF(vAggregationType = 'Rolling Average - 7',
Num(IF(Sum(Aggr(IF([$(vMeasureSelector3)] <> 0, RangeAvg(Above([$(vMeasureSelector3)], 0, 7))),
[$(vDimensionSelector)], StopDate.autoCalendar.Date
)) <> 0,

Sum(Aggr(IF([$(vMeasureSelector3)] <> 0, RangeAvg(Above([$(vMeasureSelector3)], 0, 7))),
[$(vDimensionSelector)], StopDate.autoCalendar.Date
)),Null()
),$(vMeasureSelector3Format) // Apply the formatting
))

Thanks/Mike

Re: RangeAvg(Above( Aggr( not working in line chart with date range

Qrishna — Sat, 19 Oct 2024 19:52:10 GMT

sample data and expected output pls.

Re: RangeAvg(Above( Aggr( not working in line chart with date range

ASC-Mike — Tue, 05 Nov 2024 14:38:11 GMT

Sorry for the delay as I had to put his together. Sample app attached.

To create the test case:

Filter date range for 9/3/24 to 9/30/24

Filter Comparison Period as Prior Period (this actually becomes 8/6/24 to 9/2/24 and shows at the top of app)

Filter Aggregation type as Rolling average - 7

Filter Dimension as AGGREGATE

Filter Y Axis Metric as OFD

The line chart with smooth out the current period for the correct date range 9/2 to 9/30, but the prior period is not working properly as it is including both the prior period dates and the filtered dates. I do use the dual function in the dimension so the dates on the X axis are blended for the current period and prior period which seems to work.

All other test cases for the app work except for the one described above. FYI... The line chart is actually two charts that swap depending on the dimension selector.

Current period dates are bleeding into the prior period expression:

Re: RangeAvg(Above( Aggr( not working in line chart with date range

Kushal_Chawda — Tue, 05 Nov 2024 15:01:24 GMT

@ASC-Mike Cannot see line chart as it is vizlib line chart. Can you convert it to native line chart and share it?

Re: RangeAvg(Above( Aggr( not working in line chart with date range

ASC-Mike — Tue, 05 Nov 2024 20:17:27 GMT

@Kushal_Chawda here you go with standard line charts. Thanks.

Re: RangeAvg(Above( Aggr( not working in line chart with date range

ASC-Mike — Tue, 05 Nov 2024 21:23:01 GMT

@Kushal_Chawda I saw an issue where I left a vizlib chart in there. Here is the corrected copy. Thanks/Mike