Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

stascher
Contributor

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

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

Here are your 2 options

1) Count(DISTINCT TOTAL <group> subject)

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


Capture.PNG

6 Replies

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

May be this

Count(TOTAL <event> subject)

or this

Count(TOTAL <group> subject)

stascher
Contributor

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:

ctes.png

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

ctgs.png

stascher
Contributor

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.

Here are your 2 options

1) Count(DISTINCT TOTAL <group> subject)

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


Capture.PNG

stascher
Contributor

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

Community Browser