need help

[#1]

Hi Viewers,

Please have a look at my scenario,I have taken pivot table to accomplish this.

But not getting what exactly am looking for.am getting results in two tables I need to show in one table. As I shown below.

I wanted to confirm whether its possible or not in pivot table

PFB for your reference.

Source Table:   

State	Role	hc	act	gap
ap	a1	1	1	0
ap	a2	2	1	1
ap	a3	3	2	1
tel	a1	2	1	1
tel	a2	3	3	0
tel	a3	1	1	0
tam	a1	3	1	2
tam	a2	2	1	1
tam	a3	1	1	0
kar	a1	2	2	0
kar	a2	1	1	0
kar	a3	3	2	1
krl	a1	1	1	0
krl	a2	2	1	1
krl	a3	3	3	0

Expected Output:

Role	a1			a2			a3			Status
Status	hc	act	gap	hc	act	gap	hc	act	gap	hc	act	gap	%
ap	1	1	0	2	1	1	3	2	1	6	4	2	67%
tel	2	1	1	3	3	0	1	1	0	6	5	1	83%
tam	3	1	2	2	1	1	1	1	0	6	3	3	83%
kar	2	2	0	1	1	0	3	2	1	6	5	1	50%
krl	1	1	0	2	1	1	3	3	0	6	5	1	83%
Total	9	6	3	10	7	3	11	9	2	30	22	8	74%

Please suggest me on this.

Thanks in adv!

[sunny_talwar]

This?

[sunny_talwar]

With Total at the bottom

[#4]

Yes Sunny almost reached.

But how to show totals in below as I shown above.

Please have a look at.

And also please share your script here as am not able to open your file.

[sunny_talwar · Accepted solution]

Script

Table:

LOAD * INLINE [

    State, Role, hc, act, gap

    ap, a1, 1, 1, 0

    ap, a2, 2, 1, 1

    ap, a3, 3, 2, 1

    tel, a1, 2, 1, 1

    tel, a2, 3, 3, 0

    tel, a3, 1, 1, 0

    tam, a1, 3, 1, 2

    tam, a2, 2, 1, 1

    tam, a3, 1, 1, 0

    kar, a1, 2, 2, 0

    kar, a2, 1, 1, 0

    kar, a3, 3, 2, 1

    krl, a1, 1, 1, 0

    krl, a2, 2, 1, 1

    krl, a3, 3, 3, 0

];

Dim1:

LOAD * Inline [

Dim1

1

2

];

Dim2:

LOAD * Inline [

Dim2

1

2

3

4

];

Pivot table

Dimensions

State

=Pick(Dim1, Role, 'Total')

=Pick(Dim2, 'hc', 'act', 'gap', '%')

Expression

=Pick(Dim2, Sum(hc), Sum(act), Sum(gap), If(Dim1 = 2, Num(Sum(act)/Sum(hc), '##%')))

Also, enable 'Show Partial Sums' for State dimension from presentation tab

[#6]

Sunny, Thanks for your quick response.

Just now I tried as you suggested.

Created Inline tables with dim1(1,2) and another table dim2(1,2,3,4)

Now I have taken my dimensions as

state, and in calculated dimension

=Pick(Dim1, Role, 'Total')

=Pick(Dim2, 'hc', 'act', 'gap', '%')

and Expression

=Pick(Dim2, Sum(hc), Sum(act), Sum(gap), If(Dim1 = 2, Num(Sum(act)/Sum(hc), '##%')))

Then why am not getting results as same like you.Please suggest where did am wrong.

Pls help!

[sunny_talwar]

What are you getting? May be check 'Always Fully Expanded' on the presentation tab

[#8]

Hey Sunny thank you so much for your solution really its working great now.!!!

Thanks again!!!