6 Replies Latest reply: Apr 6, 2018 8:55 AM by Sunny Talwar

# Calculating aggr while ignoring some dimensions in pivot table.

Given two tables:

subjectgroup
Abeapple

Andy

apple
Bettybanana
Brendabanana
Charleycherry
Candicecherry
Craigcherry

subject

eventinstance
Abeartery pop1
Abeartery pop2
Abeangioplasty1
Andyangioplasty1
Andybiopsy1
Bettybiopsy1
Charleyartery pop1
Charleycranial leak1
Craighernia1

I want to create a pivot table where the rows are the events, the columns are the groups and under each column are 3 measures

1) count of instances of the event for that group; count( subject)

2) count of subjects experiencing that event; count (distinct subject)

3) count of subjects experiencing that event / total number of subjects in the group (irrespective of events)

I've got it all working up  until #3.  I can't get  the expression to count total subjects in the group

Thanks for any help.

Steve

• ###### Re: Calculating aggr while ignoring some dimensions in pivot table.

May be this

Count(TOTAL <event> subject)

or this

Count(TOTAL <group> subject)

• ###### Re: Calculating aggr while ignoring some dimensions in pivot table.

Hi Sunny.   Close but not quite.  I've modified my example to display your suggestions (without the proportion part for clarity).  As you can see from the first input table there are 2 people in the apple group, 2 people in the banana group and 3 people in the cherry group. So what I'm hoping to see in this Pivot is:

• for every row under the apple group, the 3rd column will display 2 (total people in apple group)
• for every row under the banana group, the 3rd column will display 2 (total people in banana group)
• for every row under the cherry group, the 3rd column will display 3.  (total people in cherry group)

When I use Count( TOTAL <event> subject) I see:

When I use Count( TOTAL <group> subject) I see:

• ###### Re: Calculating aggr while ignoring some dimensions in pivot table.

I got it..

aggr( count(distinct total <group> subject),group,event)

Thanks anyway!

• ###### Re: Calculating aggr while ignoring some dimensions in pivot table.

1) Count(DISTINCT TOTAL <group> subject)

2) Aggr(NODISTINCT Count(DISTINCT subject), group)

• ###### Re: Calculating aggr while ignoring some dimensions in pivot table.

The second option works fine.  For your efforts I'll unmark my response and mark yours correct.

• ###### Re: Calculating aggr while ignoring some dimensions in pivot table.

Sounds good, and thank you