Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
stascher
Partner - Creator II
Partner - Creator II

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)

Demo4.png

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

1 Solution

Accepted Solutions
sunny_talwar

Here are your 2 options

1) Count(DISTINCT TOTAL <group> subject)

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


Capture.PNG

View solution in original post

6 Replies
sunny_talwar

May be this

Count(TOTAL <event> subject)

or this

Count(TOTAL <group> subject)

stascher
Partner - Creator II
Partner - Creator II
Author

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:

ctes.png

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

ctgs.png

stascher
Partner - Creator II
Partner - Creator II
Author

I got it..

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

Thanks anyway!

sunny_talwar

Here are your 2 options

1) Count(DISTINCT TOTAL <group> subject)

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


Capture.PNG

stascher
Partner - Creator II
Partner - Creator II
Author

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

sunny_talwar

Sounds good, and thank you