Pivot table object

[#1]

hi,

i have a pivot table like below.

Group	Count	UniqueCount
Team1	3	3
Team2	18
Team3	26	26

for measure column "Count" - i am using the formula "count(members)".

for measure column UniqueCount - i want to get the normal count(members), but under Team2, it should not count the members that already have been counted in team 1 and Team3.

Under Team2, we have few members from team 1 and Team 2, and i want to exclude them in my count.

is it possible?

thanks

Prasanta

[MK_QSL]

You have to create Flag for Unique/NonUnique members in Script.. Provide some sample data along with expected output !

[#3]

My data is like below.

GroupName	Member
Team1	Kaman
Team1	Karan
Team1	Deepak
Team2	Deepak
Team2	prasanta
Team2	Ashok
Team2	sagar
Team2	sanchar
Team3	Sameer
Team3	prasanta
Team3	Madan

when i will draw pivot table as mentioned above, i want to exclude Deepak and Prasanta from the count of Team2, as they are part of Team1 and Team3 respectively.

[MK_QSL · Accepted solution]

Input:
Load *, If(Match(GroupName,'Team1','Team3'),1,2) as Flag Inline
[
  GroupName, Member
  Team1, Kaman
  Team1, Karan
  Team1, Deepak
  Team2, Deepak
  Team2, prasanta
  Team2, Ashok
  Team2, sagar
  Team2, sanchar
  Team3, Sameer
  Team3, prasanta
  Team3, Madan
];


Temp:
Load GroupName, Member, If(Member <> Previous(Member),1,0) as Flag2 Resident Input 
Order By Member, Flag, GroupName;


Drop Table Input;

Now use Straight / Pivot Table

Dimension

GroupName

Expressions

COUNT(Member)

COUNT({<Flag2 = {1}>}Member)

[#5]

is it possible to do it in pivot measure expression

[MK_QSL]

I don't know.. may be someone can help you to do in front end.. But you need to think about the application performance also.

[#7]

i want to do it in expression, as i need those users in other charts and calculation

[MK_QSL]

May be try this..

Dimension

GroupName

Expression

COUNT(Member)

and

=IF(GroupName <> 'Team2',COUNT(Member),

COUNT({<GroupName = {'Team2'}, Member = E({1<GroupName = {'Team1'}>+<GroupName = {'Team3'}>}Member)>}Member))

[MK_QSL]

FYI... I haven't ignored those users at front end. The scripting is only to create flag.

[#10]

this will be difficult to manage when my number of groups will increase from 3 to more.