How to show count of values in pie chart with legend?

[#1]

Hi,

I have below data in excel file.

ID	XXApple	XXFruit	XXSavouri	XXCrisps	XXMelon
56356	0	2	0	0	2
464745	0	2	0	0	2
65365	0	0	0	0	2
56467645	0	2	0	0	0
7756763464	0	2	0	0	2
7466346	0	2	0	0	2
67647657	0	0	0	0	2
5746456	2	0	0	0	0
574634534	0	0	0	2	2
74356456	2	0	0	0	0
54476445	0	0	0	0	0
45656546	2	0	0	0	0

I need to show the count of ID percentages where column values are 2 in pie chart with legend at the side as

XXApple

XXFruit

XXSavouri

XXCrisps

XXMelon.

Please can anyone suggest me.

Thanks.

[#2]

output should be as shown in attached. Please suggest

[JonnyPoole]

Here is a sample but i'm not sure if its what you asked for. Please comment on what you might need differnt

[#4]

Thanks. This matches if the data is in excel. what if the source data is coming from ms access table. how to use cross table on access table data. sorry the one which I mentioned is sample data.  please suggest

EDIT : the format is same which I have in access table.

[saurabh5]

Hi Amelia,

first load your excel table which is crosstable into straight table then use set analysis.

Table:

CrossTable(type, Data)
LOAD ID,
XXApple,
XXFruit,
XXSavouri,
XXCrisps,
XXMelon
FROM
[..\Solve.xlsx]
(ooxml, embedded labels, table is Sheet1);

then use the expression: 

=

count(distinct {<Data={'2'}>}ID)

dimesion: type   ...............in your pie chart,nad check mark relatve in expression tab while creating your pie chart.

Regards

Saurabh

[JonnyPoole · Accepted solution]

The crosstable is still valid in access there just isn't a wizard for it :

Below will generate a qlikview table with 3 fields:

First field is 'Salesman ID' -> values are IDs straight from field with same name in access DB

2nd field is 'Fields' -> values are 'Salesman' and 'Distributor ID'... the remaining field names in the query

3rd field is 'Values' -> contains the row values from the Salesman and distributor id fields in the DB.

ODBC CONNECT TO [Salesman;DBQ=C:\Qlik Resources\Demos\10 Minute Demo\Data\Salesman.mdb];

CrossTable(Fields,Values)

LOAD `Salesman ID`,

    Salesman,

    `Distributor ID`;

SQL SELECT `Salesman ID`,

    Salesman,

    `Distributor ID`

FROM SALESMAN;

[#7]

Thanks. I did give a try the same and it worked. Thanks for giving me the solution.