topic Re: Calculating aggr while ignoring some dimensions in pivot table. in App Development

Calculating aggr while ignoring some dimensions in pivot table.

stascher — Thu, 05 Apr 2018 18:57:11 GMT

Given two tables:

subject	group
Abe	apple
Andy	apple
Betty	banana
Brenda	banana
Charley	cherry
Candice	cherry
Craig	cherry

subject	event	instance
Abe	artery pop	1
Abe	artery pop	2
Abe	angioplasty	1
Andy	angioplasty	1
Andy	biopsy	1
Betty	biopsy	1
Charley	artery pop	1
Charley	cranial leak	1
Craig	hernia	1

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.

sunny_talwar — Thu, 05 Apr 2018 19:24:38 GMT

May be this

Count(TOTAL <event> subject)

or this

Count(TOTAL <group> subject)

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

stascher — Fri, 06 Apr 2018 12:31:47 GMT

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.

stascher — Fri, 06 Apr 2018 12:40:42 GMT

I got it..

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

Thanks anyway!

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

sunny_talwar — Fri, 06 Apr 2018 12:44:03 GMT

Here are your 2 options

1) Count(DISTINCT TOTAL <group> subject)

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

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

stascher — Fri, 06 Apr 2018 12:50:36 GMT

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.

sunny_talwar — Fri, 06 Apr 2018 12:55:30 GMT

Sounds good, and thank you