RangeAvg accumulating per Month in Actual Year

[Anonymous]

Hello,

I need to make an average using the RangeAvg function, accumulating the values of the current year until previous month (if the current month is 6, until the month 5), my fact table has data by customer and a indication of which month this result refer (Column Month) and a Column Year too. Can I use the RangeAvg function for this case? Someone can show me an example?

Best Regards.

[petter]

If you have the Year and Month in two separate fields you should be able to do like this:

If( Year < Year( Today() ) OR ( Year = Year( Today() ) AND Month < Month( Today() ) ),  RangeAvg ( Above( Sum( Sales ) , 0 , RowNo() ) ) )

[petter]

[Anonymous]

Thanks for you reply!

But in my chart i don't have the Year and Month Column, only the Client Name need to appear in this object. It's possible to use RangeAvg in this situation?

[petter]

Yes you can but you don't use RangeAvg() you use the regular Avg() with a Set Expression:

   Avg( { < Year = {'$(=Year( Today() ) )'},Month={"<$(=Num( Month( Today() ) ) )"} > } Val )

The Set Expression picks out the current year and all months before the current to be used as basis for calculating the average.

A prerequisite is that the customer has his/hers values already aggregated to the month level as you said you had. If not you will have to have an Aggr()  added which makes the expression even more complicated.