Expression sum and average

[#1]

Hello guys,

I have searched a moment but I have ended up asking for some help

I cannot find the proper expression to get what I want. I am pretty sure I have to use the aggr function but well, I haven't found....

I have a table looking like this :

Category  ; Date  ; state ; area ; number of cars

A ;  01/01/2014  05:00 ; repaired ; France ;  98

B ; 06/09/2014 07:00 ; broken ; England ; 12

For each hour of one day, I have for each area, for each state, for each category, the number of cars.

So 24 hours x  3 category x 3 areas x 12 states  per day.

I would like to show in a graph the average number of cars for each state per day.  So in dimensions, I have put Date and State.  But I cannot find the right expression.

If I do it in several steps:

First, I would do :

Date ; state ; sum(NumberCars)    --> so I kind of get rid of the dimensions category and area.

And then, I could do AVG  (of sum(NumberCars)

I hope I am clear enough

any ideas ?

Thanks a lot

[jonathandienst]

Hi

For a simple average:

=Avg([number of cars])

But I suspect you want the average of the sum by category and area as well:

=Avg(Aggr(Sum([number of cars]), Category, Date, state, area))

HTH

Jonathan

[#3]

Thank you Jonathan. This formula works.

But now, I want a graph with the week in dimension instead of the date (the day).

So the right formula would be something like :

= AVG(aggr(sum(nb_cars)/7, Date, Vehicule_Statut, Parc, zone))

But for the current week, the number of days is not 7 ( eg. it will be 1 if I execute the application on Monday). I don't know how I can handle that.

thanks again & have a good day

[jonathandienst]

You could try

= Avg(aggr(Avg(nb_cars), Date, Vehicule_Statut, Parc, zone))

or

= Avg(aggr(Sum(nb_cars)/(Today() - WeekStart(Today())), Date, Vehicule_Statut, Parc, zone))

If the built in week logic is incorrect, you may have to calculate the date - weekstart(date) during load and store it in your master calendar.

HTH

Jonathan