topic Sorry, Again: AVERAGE OF SUM in QlikView

Sorry, Again: AVERAGE OF SUM

jakobjosef — Thu, 20 Dec 2018 15:48:59 GMT

Hi guys,

sorry I know there are a few threads foe this topic but I did not manage to find a solution so maybe you can help me.

I am trying to show the average of staff-costs on a monthly basis.

For each employee there are different cost centers (like "wage" or "car" or "other payro costs" or "travel-expenses").

I want to show the average over all cost centers and all months, so every month should have the same amount of staff costs.

field names: "employee", "cost center", "month" and "amount".

I already tried:

Avg( Aggr(Sum(amount), month))

Avg( Aggr(Sum(amount), month, year))

Avg( Aggr(Sum(amount), month, cost center))

and all solutions with Sum(amount) as variable $(vTotalAmount)

Anyone knows where my mistake is?

I would appreciate help very much.

Thank you
Jakob

Re: Sorry, Again: AVERAGE OF SUM

sunny_talwar — Thu, 20 Dec 2018 16:10:56 GMT

Hi Jakob -

Average can be a tricky topic and not sure if you have already seen this or not -> Average - Which average

But without seeing some snippet of your data and the output you are looking to get, it would be very difficult to give you an expression which will work right away for you.

Re: Sorry, Again: AVERAGE OF SUM

jakobjosef — Thu, 20 Dec 2018 16:12:21 GMT

Hi Sunny,

thank you for your hint, I'll read it, maybe it brings me further!

Greetings

Jakob

Re: Sorry, Again: AVERAGE OF SUM

dplr-rn — Thu, 20 Dec 2018 17:50:56 GMT

Adding to sunny's reply.
i tend to distrust avg function. i would rather calculate it myself like hic mentions as
"Sum( Amount ) / Count( distinct MonthID & '|' & CustomerID )"