Data Pivoting, not getting the result as such

[Anonymous]

Hi All,

I have a data like below.

E.No	Type	Salary	Mobile
1	basic	100.00	34343
1	DA	50.00	34353
1	Spcl Allowance	50.00	4254
1	PF	10.00	244542
2	basic	150.00	245234
2	DA	70.00	5335
2	Spcl Allowance	80.00	5234423
2	PF	15.00	3434

Want to Result like below, tried but didn't get the result. can you please help on this.

Type-List

basic

DA

Spcl Allowance

PF

Type-List			E.No	basic	DA	PF	Total
basic			1	100	50	10	160
DA			2	150	70	15	235
Spcl Allowance
PF

E.No	basic	DA	Total
1	100	50	150
2	150	70	220

Note: I don't want to see "Special Allowance" & "PF" Fields in my table, and want to have some Basic+DA as Total.

PFA for Exce-File Attachment.

Thanks,

Qk

[Frank_Hartmann]

create straight table with dimension:

E.No

and add expressions:

1. sum({<Type={'basic'}>}Salary)

2. sum({<Type={'DA'}>}Salary)

3. sum({<Type={'basic'}>}Salary)+sum({<Type={'DA'}>}Salary)

hope that helps!

[vinieme12]

Or you can still use a pivot table with below dimensions

1) E.No

2) =if(wildmatch(type,'basic','da')>0,type). << check Suppress when value is null

Expression

Sum(Salary)

In the presentation tab  check partial sum for E.no.

[Anonymous]

Yes Frank Hartmann,

You are correct, and one more thing that

From List-Box

"If i select 'Basic,PF' it has to display Basic, PF as columns in table and has Total as Basic+PF,

"If i select 'Basic, PF, DA' it has to display Basic, PF, TA as Columns and has Total as Basic+PF+DA.

Can you please help on, how to achieve this.

Thanks,

Qk

[sunny_talwar]

I suggest using a pivot table instead of a straight table.

Dimensions

1) E.No

2) Type

Expressions

Sum({<Type *= {'basic', 'PF', 'DA'}>}Salary)

For Total select partial sum on the presentation tab...

When nothing is selected

When basic and DA is selected

Used the sample attached by franky_h79‌ and made modifications to provide this example.

Best,

Sunny