How to show percentages in pivot for each column value?

[#1]

Hi,

I have pivot chart data as below in qvw.

Flag	MONTH	Grand Total	Oct 2013	Nov 2013	Dec 2013	Jan 2014	Feb 2014	Mar 2014	Apr 2014	May 2014	Jun 2014
ALLOW		1176	8	53	30	32	17	36	36	49	28
NOT ALLOW		783	5	34	30	29	15	28	31	39	16
ENTER		84	2	15	9	3	2	6	5	5	6
AUDIT		322	1	4	8	1	3	3	13	5	5
CONVERSION		373	2	16	9	4	2	8	5	9	8

I need the pivot chart data should show the percentages also. so, need output as below

Flag	MONTH	Grand Total	Oct 2013	Nov 2013	Dec 2013	Jan 2014	Feb 2014	Mar 2014	Apr 2014	May 2014	Jun 2014
ALLOW		1176	8	53	30	32	17	36	36	49	28
NOT ALLOW		783	5	34	30	29	15	28	31	39	16
NOT ALLOW/ALLOW		66.00%	62.50%	64.15%	100.00%	90.63%	88.24%	77.78%	86.11%	79.59%	57.14%
ENTER		84	2	15	9	3	2	6	5	5	6
ENTER/ALLOW		7.14%	25.00%	28.30%	30.00%	9.38%	11.76%	16.67%	13.89%	10.20%	21.43%
AUDIT		322	1	4	8	1	3	3	13	5	5
AUDIT/ALLOW		27.38%	12.50%	7.55%	26.67%	3.13%	17.65%	8.33%	36.11%	10.20%	17.86%
CONVERSION		373	2	16	9	4	2	8	5	9	8
CONVERSION/ALLOW		31.72%	25.00%	186.67%	30.00%	12.50%	11.76%	22.22%	13.97%	18.37%	28.57%

can anyone suggest me how to calculate the percentages to show at the bottom of each value.

Thanks.

[sinanozdemir]

Hi Amelia,

Maybe something like this:

I am also attaching the screenshots as well as qvw:

First - create a dummy dimension and insert the below expression, and call this dimension Comparison:

Second - in the expression tab, use the below script:

Third - in the presentation tab, check partial sum for the dummy dimension called Comparison:

There are other intricacies involved and if you cannot figure out the other parts, let me know.

Thanks.

[antoniotiman]

Hi Amelia,

I have used ValueList() Function

ValueList('ALLOW','NOT ALLOW','NOT ALLOW/ALLOW','ENTER','ENTER/ALLOW','AUDIT','AUDIT/ALLOW','CONVERSION','CONVERSION/ALLOW')

See Attachment

Regards

[#4]

Thanks. I have used this. I can able to see the percentages. I need a small amendment in format and layout of chart.

actually I have used the below script to get the Flag values.

DATA:
LOAD ID,
     Status,
     If (MixMatch([Status],'CM',
'CS',
'CP',
'DTAS',
'CP2',
'RA',
'RW',
'DR',
'OFF',
'DD',
'PD',
'SSI',
'DEC',
'HEA',
'APP',
'MEE1',
'MEE2',
'MEE3',
'AL',
'TE',
'PO',
'CL',
'Completions',
'RE',
'IN',
'OH',MixMatch(NOT_LOY,'-1')),Dual('ALLOW',1),
     If(MixMatch(NOT_LOY,'-1'),Dual('NOT ALLOW',2),
     If(MixMatch([Status],'CM',
     'CS',
'CP',
'DTAS',
'CP2',
'RA',
'DRAF',
'RW',
'DR',
'OFF',
'DD',
'PD',
'SSI',
'DEC',
'HEA',
'APP',
'FOFF',
'MEE1',
'MEE2',
'MEE3',
'AL',
'IN',
'OH'),Dual('AUDIT',3),
If(MixMatch([Status],'TE',
'PO',
'CL',
'COM',
'RE'),Dual('ENTER',4),
     If(MixMatch([Status],
'CS',
'CP',
'DTAS',
'CP2',
'RA',
'RW',
'DR',
'OFF',
'DD',
'PD',
'SSI',
'DEC',
'HEA',
'APP',
'MEE1',
'MEE2',
'MEE3',
'AL',
'TE'),Dual('CONVERSION',5),
)))))                           as RET,
   
   
     DATE,
     ALLOWED_BY,
     LAST_BY
FROM
[Dashboard QVD\DATA.qvd]
(qvd)
Where Match(Status,'CM',
'CS - Allocation',
'CS',
'CP',
'DTAS',
'CP2',
'RA',
'DRAF',
'RW',
'DR',
'OFF',
'DD',
'PD',
'SSI',
'DEC',
'HEA',
'APP',
'FOFF',
'MEE1',
'MEE2',
'MEE3',
'AL',
'TE',
'PO',
'CL',
'COM',
'RE',
'IN',
'OH',
'NOT ALLOW'
);

DATA1:
LOAD *,

          Dual('ALLOW_withoutCD',6) as Flag

Resident DATA

Where MixMatch([Status],'CM',
'CM',
   'CS - Allocation',
'CS',
'CP',
'DTAS',
'CP2',
'RA',
'DRAF',
'RW',
'DR',
'OFF',
'DD',
'PD',
'SSI',
'DEC',
'HEA',
'APP',
'FOFF',
'MEE1',
'MEE2',
'MEE3',
'AL',
'TE',
'PO',
'CL',
'COM',
'RE',
'IN',
'OH',
'NOT ALLOW');

Concatenate (DATA1)

LOAD *,

          Dual('ALLOW',1) as Flag

Resident DATA

Where MixMatch([Status], 'CM',
  
'CS',
'CP',
'DTAS',
'CP2',
'RA',
'DRAF',
'RW',
'DR',
'OFF',
'DD',
'PD',
'SSI',
'DEC',
'HEA',
'APP',
'FOFF',
'MEE1',
'MEE2',
'MEE3',
'AL',
'TE',
'PO',
'CL',
'COM',
'RE',
'IN',
'OH',
'NOT ALLOW',MixMatch(NOT_APPOINTED,'-1'));

Concatenate (DATA1)

LOAD *,

          Dual('NOT ALLOW',2) as Flag

Resident DATA

Where MixMatch(NOT_APPOINTED,'-1');

Concatenate (DATA1)

LOAD *,

          Dual('AUDIT',4) as Flag

Resident DATA

Where MixMatch([Status], 'CM',
  
'CS',
'CP',
'DTAS',
'CP2',
'RA',
'DRAF',
'RW',
'DR',
'OFF',
'DD',
'PD',
'SSI',
'DEC',
'HEA',
'APP',
'FOFF',
'MEE1',
'MEE2',
'MEE3',
'AL',
'IN',
'OH');

Concatenate (DATA1)

LOAD *,

          Dual('ENTER',3) as Flag

Resident DATA

Where MixMatch([Status],'TE',
'PO',
'CL',
'COM',
'RE');

Concatenate (DATA1)

LOAD *,

          Dual('CONVERSION',5) as Flag

Resident DATA

Where MixMatch([Status],
'CM',
   'CS',
'CP',
'DTAS',
'CP2',
'RA',
'DRAF',
'RW',
'DR',
'OFF',
'DD',
'PD',
'SSI',
'DEC',
'HEA',
'APP',
'FOFF',
'MEE1',
'MEE2',
'MEE3',
'AL',
'TE');

Drop Table DATA;
Exit Script;

so, I need the pivot table please now which shows as below

Status	Oct 2013	Nov 2013	Dec 2013	Jan 2014	Feb 2014	Mar 2014	Apr 2014	May 2014	Jun 2014
CM		1				1		1
CS
CP		1
DTAS
CP2
RA
DRAF
RW								1
DR
OFF						1		2
DD								1
PD									2
SSI
DEC		1						1	2
HEA	1			1					1
APP						1			1
FOFF	1	12		3	2	4	5	4	4
MEE1								1	2
MEE2	1
MEE3		1
AL		2				1
TE									1
PO
NOT ALLOW	6	34	30	28	15	27	31	42	23
ALLOW	9	52	30	32	17	35	36	53	36
NOT   ALLOW/ALLOW %	67%	65%	100%	88%	88%	77%	86%	79%	64%
ENTER	3	15	0	4	2	6	5	5	9
ENTER/ALLOW%	33%	29%	0%	13%	12%	17%	14%	9%	25%
AUDIT	0	3	0	0	0	2	0	6	4
AUDIT/ALLOW %	0%	6%	0%	0%	0%	6%	0%	11%	11%
CONVERSION	3	18	0	4	2	8	5	11	12
CONVERSION/ALLOW%	33%	34%	0%	13%	12%	21%	14%	20%	33%

Would it be possible to get like this? Please help me ! I am not worrying about the data. only thing I need is format of pivot table?

output need shown in attached.

Thanks.

[#5]

please can help me.

[antoniotiman]

Amelia,

theoretically Yes, but with ValueList is very hard.

I you suggest 2 charts.

Regards

[#7]

Thanks. I have suggested this to user earlier before I replied to the post. the user wants only the merge of two charts into one. would there be any other solution for this ? please suggest

[antoniotiman]

Try

Dimension:

=ValueList('CM','CS','CP','DTAS','ALLOW','NOT ALLOW','NOT ALLOW/ALLOW','ENTER','ENTER/ALLOW','AUDIT','AUDIT/ALLOW','CONVERSION','CONVERSION/ALLOW')

Expression

If(Match(ValueList('CM','CS','CP','DTAS','ALLOW','NOT ALLOW','NOT ALLOW/ALLOW','ENTER','ENTER/ALLOW','AUDIT','AUDIT/ALLOW','CONVERSION','CONVERSION/ALLOW'),'CM','CS','CP','DTAS')

,YourExpression,
Pick(Match(ValueList('CM','CS','CP','DTAS','ALLOW','NOT ALLOW','NOT ALLOW/ALLOW','ENTER','ENTER/ALLOW','AUDIT','AUDIT/ALLOW','CONVERSION','CONVERSION/ALLOW'),
'ALLOW','NOT ALLOW','NOT ALLOW/ALLOW','ENTER','ENTER/ALLOW','AUDIT','AUDIT/ALLOW','CONVERSION','CONVERSION/ALLOW'),
Sum({<Flag={'ALLOW'}>} Value),
Sum({<Flag={'NOT ALLOW'}>} Value),
Num(Sum({<Flag={'NOT ALLOW'}>} Value)/Sum({<Flag={'ALLOW'}>} Value),'#.##0,00 %'),
Sum({<Flag={'ENTER'}>} Value),
Num(Sum({<Flag={'ENTER'}>} Value)/Sum({<Flag={'ALLOW'}>} Value),'#.##0,00 %'),
Sum({<Flag={'AUDIT'}>} Value),
Num(Sum({<Flag={'AUDIT'}>} Value)/Sum({<Flag={'ALLOW'}>} Value),'#.##0,00 %'),
Sum({<Flag={'CONVERSION'}>} Value),
Num(Sum({<Flag={'CONVERSION'}>} Value)/Sum({<Flag={'ALLOW'}>} Value),'#.##0,00 %')
))

In ValueList (Dimension and Expression ) add other Values .

[#9]

Thanks. tried this. chart is not populating. showing only chart name.

[antoniotiman]

Have You changed YourExpression

with Your Expression ?