Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
ASC-Mike
Contributor II
Contributor II

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

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

 

Labels (1)
5 Replies
Qrishna
Master
Master

sample data and expected output pls. 

ASC-Mike
Contributor II
Contributor II
Author

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:

ASCMike_0-1730817426303.png

 

Kushal_Chawda

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

ASC-Mike
Contributor II
Contributor II
Author

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

ASC-Mike
Contributor II
Contributor II
Author

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