Calculate the average of a column with below() across YRMON rows

[zhj0735]

Hi, I have a problem trying to calculate the average of a column inside the Vizlib pivot table, specifically with the below() function inside. 

First I start with average calculations of actual sales  AVG(vol_act), it works correctly and shows the average on the top of the "Total" row perfectly.  Then, I tried to calculate the average of the lagged "vol_act" without luck:

1. I firstly put Agg(Below(vol_act),YRMON), the lagged monthly output are weird. Instead of showing the previous month, it picked the random month of sales.

2. Though avg(Agg(Below(vol_act),YRMON) is calculated right, it does not calculate the right lagged month. 

[Oleg_Troyansky · Accepted solution]

Hi,

You must be referring to the AGGR() function... When you use Below() within AGGR(), the order of months is determined by the load order of the field YRMON, not by the chart's sort order, which appears to be sorted by month in the descending order. So in your case, you should sort the data within AGGR() by month in the descending order too.

Also, the AGGR() function returns an array of values, which may cause problems. Enclosing it in the Avg() function is a good idea. So, I believe this should work better:

avg(

       Aggr(

                    Below(vol_act),

                    (YRMON, (NUMERIC, DESC))

      )

)

Cheers,

[zhj0735]

That works perfectly! Thanks a lot Oleg!