I need to have an average on the top of each column of a pivot table in qliksense

[Madhab77]

Hi All I have the following pivot table (without the red color one).

Employee	Appointment Date	Average
	06-14-2021	06-15-2022	06-16-2023	06-17-2024	06-18-2025	and so on
	Avg: 3.33	Avg: 3.35	Avg: 3.16	Avg: 2.66	Avg: 2.5	Avg: 3.16
A	4	1	5	4	4	4
B	3	-	4	3	3	4
D	5	5	6	5	-	-
E	3	5	-	4	4	-
F	-	4	-	-	4	5
G	5	6	4	-	-	6

 Now I need as the Average value in each column as displayed in the image. It should also alter the result accordingly when I select employee(s).

Can anybody help me out to achieve this?

Thanks in Advance.

Madhab Sikder

[Vegar]

You could try to use rangeavg () to solve your problem.

help.qlik.com - rangeavg()

[Madhab77]

Hi Vegar,

Thanks for you reply. But unfortunately, it is not giving the desired result. It may work for simple table, but it is not working for pivot table.

Can you please provide some example, if you have anything handy.

Thanks,

Madhab Sikder

[Vegar]

Ok i see. 

What about an avg aggr avg expression then?

Avg(Aggr(Avg(Value), [Appointment date])) 

[Saravanan_Desingh]

One  simple solution is.

tab1:
LOAD *, RangeSum(Value,0) As V2;
LOAD * INLINE [
    Employee, Appointment Date, Value
    A, 6/14/2021, 4
    A, 6/15/2022, 1
    A, 6/16/2023, 5
    A, 6/17/2024, 4
    A, 6/18/2025, 4
    A, 6/19/2026, 4
    B, 6/14/2021, 3
    B, 6/15/2022, 
    B, 6/16/2023, 4
    B, 6/17/2024, 3
    B, 6/18/2025, 3
    B, 6/19/2026, 4
    C, 6/14/2021, 5
    C, 6/15/2022, 5
    C, 6/16/2023, 6
    C, 6/17/2024, 5
    C, 6/18/2025, 
    C, 6/19/2026, 
    D, 6/14/2021, 3
    D, 6/15/2022, 5
    D, 6/16/2023, 
    D, 6/17/2024, 4
    D, 6/18/2025, 4
    D, 6/19/2026, 
    E, 6/14/2021, 
    E, 6/15/2022, 4
    E, 6/16/2023, 
    E, 6/17/2024, 
    E, 6/18/2025, 4
    E, 6/19/2026, 5
    F, 6/14/2021, 5
    F, 6/15/2022, 6
    F, 6/16/2023, 4
    F, 6/17/2024, 
    F, 6/18/2025, 
    F, 6/19/2026, 6
];

[Saravanan_Desingh]

Do the changes in the Presentation tab.

[Madhab77]

Hi Saran,

Thank you very much for you info.. But unfortunately I am not getting the exact result in QlikSense, it may work for QlikView. The reason may be because of the subtotal option/partial sum, in Qliksense, the subtotal/partial sum option is not available.

Please find the screenshot which I got after implementing your logic.

Here I am getting same average for all the columns. If I select a particular date, then I am getting the exact result, but not when multiple dates or non.

Thanks

Madhab

[Madhab77]

Hi Vegar,

Still I am not getting my desired result.

Note: I want this in QlikSense.

Below is the screenshot which I am getting after using the Avg(Aggr(Avg(Value), [Appointment date])) 

Please let me know if  I am missing anything.

Thanks 

Madhab Sikder

[Vegar · Accepted solution]

What if you calculate avg like this: sum(Value)/count(Employee)

See attached qvf

[Madhab77]

Hi Vegar,

Thank you so much. It worked this time. You helped a lot.

Thank you once again.

Madhab Sikder