average over chart dimensions

[#1]

Hi

My data has monthly snapshots of objects containing a "Number_Of_Items" field. I create a Quarter and Year field from the current month the data is from.

I use a drill-down group as Dimension with Year, Quarter, Month.

I show the sum(Number_Of_Items) as expression in a bar-chart.

Selecting the Year the bars show the sum of all the monthly values in the year where I would like to show the average of the monthly numbers.

Reading the help file tells me that avg is using the charts dimensions but avg(sum(Number_Of_Items)) does not return a value.

Do I need to divide the total Number_Of_Items by the number of month's for year and quarter shown in the chart myself?

Thanks

Jürg

[sparur]

I'm not sure that understand your task, but try use AVG(Number_Of_Items)

[#3]

Hi Anatoly

I have many objects within each month

avg(Number_Of_Items) gives me the average over all the objects but not over the montly totals within the year.

Juerg

[sparur]

mya be, you should use such expression:

avg(TOTAL <Month> Number_Of_Items)

If I don't understand again, please can you give a small example of your data and explain me what you want to achieve in each month value

[#5]

Anatoly

I have tried to make an example.

I could divide the sum(Number_Of_Items) by number of month's but for the current year I would need to count the existing number of month and can not use 12 as divisor.

Juerg

[#6]

Hello Juerg,

I understand that you want to calculate the average over the month's sum.
So first calculate all the sums for the months, and then calculate the avg ... all along the chart dimensions.

This sounds to me as you could use the 'aggr' function.
This function returns a set of values 'along given dimensions' on which you can do further calculations.

Simplified I imagine something like avg( aggr(sum(Items), Month) ).

hth,
Thilo

[sparur]

Ok, Juerg.

I undestood now 🙂

see in my example

[#8]

Thanks

Looks better and I will have a look into the AGGR function.

As the year level looks good selecting the Month level however shows equal values for object 1 and 2 which is not correct.

Jürg