topic YTD average calc in QlikView

YTD average calc

alan_grn — Mon, 05 Jul 2010 11:50:16 GMT

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.

YTD average calc

Miguel_Angel_Baeyens — Mon, 05 Jul 2010 12:39:42 GMT

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)

YTD average calc

alan_grn — Mon, 05 Jul 2010 13:26:47 GMT

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.

YTD average calc

alan_grn — Mon, 05 Jul 2010 13:57:06 GMT

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?

YTD average calc

Miguel_Angel_Baeyens — Mon, 05 Jul 2010 15:03:10 GMT

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.