topic Re: Average in Pivot table in QlikView

Average in Pivot table

guillaume_gorli — Mon, 18 Jan 2021 16:13:42 GMT

Dear all,

I am using a database listing time spent by employees on different activities, each employee being defined by a status A,B or C.

Variables coming from the database are :

Date
Employee as name of the employee
Status as Status of the employee
Off as Hours spent by employee being Off
Sick as Hours spent by employee being on sick
Home Office as Hours spent by employee working from home
On site as Hours spent by employee working on site
I need to produce a pivot table with a total by status (in line) but also an average by week (in column).

What i get from the app is shown in below pic :

What i should get is the same thing except that im not looking for a Total regadring the date but an average figure, meaning :

Attached is xlxs database as well as qvw for those who want to help.

Thanks in advance

Guillaume

Re: Average in Pivot table

skamath1 — Mon, 18 Jan 2021 16:49:10 GMT

Since you have entry by date. Instead of using Sum, can you use AVG and see if it works?

Re: Average in Pivot table

Frank_Hartmann — Wed, 20 Jan 2021 08:57:42 GMT

Can you explain how you calculate the avg?
e.g.

C- Homeoffice: 2+2+2+2+6+4+6 = 24/7 <> 3
C- Working on site: 14+14+14+14+18+12+10 = 96/7 <> 12

And also the calculation of Avg Total Values (11,11,5,16) is not clear.

Re: Average in Pivot table

guillaume_gorli — Thu, 21 Jan 2021 20:10:54 GMT

Indeed Franck, my excel formulas in the table was wrong.

Below is how the corrected average looks like :

Re: Average in Pivot table

Frank_Hartmann — Thu, 21 Jan 2021 21:50:37 GMT

Here you go. see attached qvw

Hope this helps

Re: Average in Pivot table

guillaume_gorli — Fri, 22 Jan 2021 08:28:20 GMT

Franck, this helps a LOT !

Thanks you very much

Guillaume