Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
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)
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.
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?
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.