topic Re: Expression sum and average in QlikView

Expression sum and average

Mon, 22 Sep 2014 15:03:29 GMT

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

Re: Expression sum and average

jonathandienst — Mon, 22 Sep 2014 15:11:46 GMT

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

Re: Expression sum and average

Tue, 23 Sep 2014 09:45:47 GMT

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

Re: Expression sum and average

jonathandienst — Thu, 25 Sep 2014 06:37:03 GMT

You could try

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

= 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