topic average over chart dimensions in QlikView

average over chart dimensions

Wed, 03 Nov 2010 08:17:15 GMT

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

average over chart dimensions

sparur — Wed, 03 Nov 2010 08:37:01 GMT

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

average over chart dimensions

Wed, 03 Nov 2010 09:23:41 GMT

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

average over chart dimensions

sparur — Wed, 03 Nov 2010 09:33:32 GMT

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

average over chart dimensions

Wed, 03 Nov 2010 10:14:16 GMT

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

average over chart dimensions

Wed, 03 Nov 2010 10:41:07 GMT

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

average over chart dimensions

sparur — Wed, 03 Nov 2010 10:41:43 GMT

Ok, Juerg.

I undestood now 🙂

see in my example

average over chart dimensions

Wed, 03 Nov 2010 13:48:08 GMT

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