Help with Pivot/Straight table

[#1]

Hi -

I am trying to create a table in QlikView similar to the Excel pivot table below:

Times Repeated	Total	%
1	844	76
2	203	18
3	47	4
4	16	1
5+	5	1

The issue I have is I am using the dimension EmployeeNo and using the following expression - Count(EmployeeNo) and an example of my results show as follows:

Employee No	Times Repeated
1	8
2	7
3	6
4	6
5	6
6	4
7	4
8	4
9	4
10	4

How can I get the QlikView pivot table to show similar to the Excel - I have tried hiding the EmployeeNo to only show the Times Repeated but can not group the Times Repated totals to show 1,2,3,4,5+ and then get percentages like the excel pivot above.

Thanks.

[its_anandrjs]

Hi,

I suggest you have to use group by function and plot the frequency of the Employee no.

Regards,

Anand

[alexandros17 · Accepted solution]

Your dimension will be:

=

if(aggr(Count(emp),emp)<5,aggr(Count(emp),emp),

'5+'

)

your expression:

count

(aggr(Count(emp),emp))

Hope it helps

[#4]

http://www.eteamz.com/bismillah1/

[its_anandrjs]

Hi,

Load data soe thing like below

Data :

load * inline

[

Employee No

1

1

1

1

1

1

1

1

2

2

2

2

2

2

2

3

3

3

3

3

3

4

4

4

4

4

4

5

5

5

5

5

5

6

6

6

6

7

7

7

7

8

8

8

8

9

9

9

9

10

10

10

10

];

LOAD

[Employee No],

Count([Employee No]) as Frequency

Resident Data Group by [Employee No];

DROP Table Data;

In Expresion

[Employee No]

and in Dimension

sum(Frequency)

Thanks & Best Regards,

   Anand

[#6]

Thank you was able to solve the issue with your help