Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

Pivot Table Calculated Fields

Dear All,

I have got below pivot table produced to show parts usage across different schedule numbers. Dimensions are Part Number, Part Description and Schedule while my expression is sum of used quantity as illustrated below.  I have also got Total QTY used per part numbers on column I. When the selections are made number of schedule changes which could go up to 100s.

What I would like to do next is to get calculations shown on column K, L, M, N and O. And I would like to get column K to O right after column B if possible.

No of Occurrence is number of cells that sum (Used QTY) is greater than 0 or not null for the respective part number (I have got pivot table to show null or missing values as 0).

% Occurrence is No of Occurrence / total unique schedule number (6 in below case but changes depending on the selections).

Average usage is average (C to H)

Max Usage is Max (C to H) and Min Usage is Min (C to H).

A

B

C

D

E

F

G

H

I

K

L

M

N

O

1

Part Number

Part Description

Schedule 1

Schedule 2

Schedule 3

Schedule 4

...

...

Total Qty used (C2+H2)

No of Occurrence

% Occurrence

Average usage

Max usage

Min Usage

2

a

..

2

1

10

5

1

5

24

6

100%

  1. 4.00

10

1

3

b

..

3

-

2

6

4

-

15

4

67%

  1. 3.75

6

2

4

c

..

5

4

5

4

-

4

22

5

83%

  1. 4.40

5

4

5

d

..

8

6

-

-

6

5

25

4

67%

  1. 6.25

8

5

6

e

..

-

-

-

5

2

-

7

2

33%

  1. 3.50

5

2

7

f

..

4

8

7

5

3

8

35

6

100%

  1. 5.83

8

3

Hope it is clear. Many thanks for all the support in advance.

7 Replies
Partner
Partner

Re: Pivot Table Calculated Fields

Please take a look at the attached qvw.

2017-02-22_9-30-21.png

Not applicable

Re: Pivot Table Calculated Fields

Hi Alberto,

Thanks for your response. I am unable to open qvw attachments. Is there another way you can show me what needs to be done?

Also, seems like you have taken my table as a simple table and made a pivot or straight table based on it if I am correct. However the table illustrated in my question is already a pivot table. I have to add the required calculations in it. Please confirm if that is correct. 

Many thanks

Partner
Partner

Re: Pivot Table Calculated Fields

You are correct I loaded the data as it was shown on the image. Can you post a small sample of data in order to validate the structure? then I can share with you the expressions as text...

Not applicable

Re: Pivot Table Calculated Fields

Hi Alberto,

Here below is a small sample of data.

Thanks

   

Part NumberPart DescriptionUsed QTYSchedule Number
1a8SCHEDULE 1
2b6SCHEDULE 1
3c10SCHEDULE 1
4d2SCHEDULE 1
5e1SCHEDULE 1
6f1SCHEDULE 1
7g4SCHEDULE 1
8h1SCHEDULE 1
9i2SCHEDULE 1
10j1SCHEDULE 1
11k1SCHEDULE 1
12l1SCHEDULE 1
13m2SCHEDULE 1
14n1SCHEDULE 1
15o1SCHEDULE 1
16p9SCHEDULE 1
17q4SCHEDULE 1
18r3SCHEDULE 1
19s14SCHEDULE 1
20t3SCHEDULE 1
1a4SCHEDULE 2
2b1SCHEDULE 2
3c4SCHEDULE 2
4d2SCHEDULE 2
5e4SCHEDULE 2
6f4SCHEDULE 2
7g4SCHEDULE 2
8h1SCHEDULE 2
9i3SCHEDULE 2
10j4SCHEDULE 2
11k4SCHEDULE 2
12l4SCHEDULE 2
13m1SCHEDULE 2
14n3SCHEDULE 2
1a4SCHEDULE 3
2b4SCHEDULE 3
3c1SCHEDULE 3
4d1SCHEDULE 3
5e4SCHEDULE 3
6f2SCHEDULE 3
7g2SCHEDULE 3
8h3SCHEDULE 3
9i1SCHEDULE 3
10j4SCHEDULE 3
11k8SCHEDULE 3
12l4SCHEDULE 3
13m4SCHEDULE 3
14n4SCHEDULE 3
15o2SCHEDULE 3
16p5SCHEDULE 3
17q4SCHEDULE 3
1a4SCHEDULE 4
2b20SCHEDULE 4
3c1SCHEDULE 4
4d2SCHEDULE 4
5e1SCHEDULE 4
6f4SCHEDULE 4
7g50SCHEDULE 4
8h1SCHEDULE 4
9i12SCHEDULE 4
10j28SCHEDULE 4
11k5SCHEDULE 4
12l4SCHEDULE 4
13m2SCHEDULE 4
1a2SCHEDULE 6
2b1SCHEDULE 6
3c25SCHEDULE 6
4d1SCHEDULE 6
5e3SCHEDULE 6
6f5SCHEDULE 6
7g8SCHEDULE 6
8h4SCHEDULE 6
9i1SCHEDULE 6
10j4SCHEDULE 6
1a1SCHEDULE 5
2b1SCHEDULE 5
3c1SCHEDULE 5
4d1SCHEDULE 5
5e2SCHEDULE 5
6f4SCHEDULE 5
7g2SCHEDULE 5
8h40SCHEDULE 5
9i4SCHEDULE 5
10j1SCHEDULE 5
11k1SCHEDULE 5
12l2SCHEDULE 5
13m28SCHEDULE 5
14n1SCHEDULE 5
15o1SCHEDULE 5
16p1SCHEDULE 5
1a2SCHEDULE 5
2b3SCHEDULE 5
3c40SCHEDULE 5
4d4SCHEDULE 5
5e1SCHEDULE 5
6f1SCHEDULE 5
7g3SCHEDULE 5
8h1SCHEDULE 5
9i1SCHEDULE 5
10j1SCHEDULE 5
11k4SCHEDULE 5
1a1SCHEDULE 5
2b1SCHEDULE 5
3c1SCHEDULE 5
4d1SCHEDULE 5
5e1SCHEDULE 5
6f2SCHEDULE 5
7g3SCHEDULE 5
8h20SCHEDULE 5
9i20SCHEDULE 5
10j4SCHEDULE 5
11k1SCHEDULE 5
12l1SCHEDULE 5
13m3SCHEDULE 5
14n1SCHEDULE 5
Partner
Partner

Re: Pivot Table Calculated Fields

Here's my suggestion:

Total Qty used: Sum([Used QTY])

No Occurrence: RangeSum(count({$<[Used QTY]*={'>0'}>}[Part Number]))

% occurrence: RangeSum(count({$<[Used QTY]*={'>0'}>}[Part Number]))/(Aggr(count([Used QTY]),[Part Number]))  

Average: Avg([Used QTY])

Max usage: Max([Used QTY])

Min usage: Min([Used QTY])

Not applicable

Re: Pivot Table Calculated Fields

Many thanks Alberto,

I have used the formulas provided as expressions but it gives error.  Also once the calculations done they are done for all schedule numbers in pivot table which can go up to 550. This increases the column number to 1100. Is it possible to get these calculations right next to total as one column? Could you please also send me the final qvw file you have managed to achieve the calculations? I will get our team to open it.

Appreciate your help.

Many thanks

Murat

Partner
Partner

Re: Pivot Table Calculated Fields

Please find the qvw for your reference. Note that expressions will need to be adjusted depending on the granularity of your chart but I think it's a good way to get you started.