Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Pivot table object

hi,

i have a pivot table like below.

   

GroupCountUniqueCount
Team133
Team218
Team32626

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Pivot table object

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)

12 Replies
MVP
MVP

Re: Pivot table object

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

Not applicable

Re: Pivot table object

My data is like below.


  

GroupNameMember
Team1Kaman
Team1Karan
Team1Deepak
Team2Deepak
Team2prasanta
Team2Ashok
Team2sagar
Team2sanchar
Team3Sameer
Team3prasanta
Team3Madan

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.

MVP
MVP

Re: Pivot table object

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)

Not applicable

Re: Pivot table object

is it possible to do it in pivot measure expression

MVP
MVP

Re: Pivot table object

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.

Not applicable

Re: Pivot table object

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

MVP
MVP

Re: Pivot table object

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))

MVP
MVP

Re: Pivot table object

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

Not applicable

Re: Pivot table object

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

Community Browser