Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Here are your 2 options
1) Count(DISTINCT TOTAL <group> subject)
2) Aggr(NODISTINCT Count(DISTINCT subject), group)
May be this
Count(TOTAL <event> subject)
or this
Count(TOTAL <group> subject)
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:
When I use Count( TOTAL <event> subject) I see:
When I use Count( TOTAL <group> subject) I see:
I got it..
aggr( count(distinct total <group> subject),group,event)
Thanks anyway!
Here are your 2 options
1) Count(DISTINCT TOTAL <group> subject)
2) Aggr(NODISTINCT Count(DISTINCT subject), group)
The second option works fine. For your efforts I'll unmark my response and mark yours correct.
Sounds good, and thank you