YTD average calc

[alan_grn]

I am trying to ascertain the best way to do a YTD average calc. My app has a field called balance and is the user selects a single date.

I am trying to get the average from start of year to date selected.

[Miguel_Angel_Baeyens · Accepted solution]

Hello Alan,

One way to achieve it would be using Set Analysis, given that the user has selected one date

Sum({< DateField = {">=$(=Date(YearStart(vSelectedDate)))<=$(=Date(vSelectedDate))"} >} Balance)

Using DateField as the field with dates you want to use and vSelectedDate a variable in which you store the date selected. Instead of the variable, though, you can use

GetFieldSelections(DateField)

[alan_grn]

Thanks.

And if I wanted the average would I just replace Sum with average or would I take the above and divide by the count of the above?

Hope that makes sense.

[alan_grn]

Sorry Just had a rethink. What I am trying to do is get the average balance on each account and them sum it through for customer, area etc.

Maybe use of Aggr?

[Miguel_Angel_Baeyens]

Hello Alan,

Depending on how your charts are designed and which your dimensions are, aggr() may be a solution. An to your question above yes, set analysis may be used on any aggregation expression you need.