topic Re: Help with Pivot/Straight table in QlikView

Help with Pivot/Straight table

Mon, 06 May 2013 15:27:15 GMT

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.

Re: Help with Pivot/Straight table

its_anandrjs — Mon, 06 May 2013 15:55:19 GMT

Hi,

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

Regards,

Anand

Re: Help with Pivot/Straight table

alexandros17 — Mon, 06 May 2013 16:08:02 GMT

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

Re: Help with Pivot/Straight table

Mon, 06 May 2013 16:34:27 GMT

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

Re: Help with Pivot/Straight table

its_anandrjs — Mon, 06 May 2013 16:49:18 GMT

Hi,

Load data soe thing like below

Data :

load * inline

[

Employee No

];

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

Re: Help with Pivot/Straight table

Mon, 06 May 2013 20:03:21 GMT

Thank you was able to solve the issue with your help