Nested expressions in measure

[pgloc2020]

Hello All,

I have a publication data where there are 1 or more author on a book and also 1 or more claimant on that same book. 

I need to create a bar chart to group of author/claimants based on their count. 

for example, data is looking like below table- 

Book Name	Authors	Claimant
Book A	Y	Y
Book A	N	Y
Book B	Y	Y
Book B	Y	Y
Book B	Y	N
Book B	Y	N
Book B	Y	N
Book C	Y	Y
Book C	N	Y
Book D	Y	Y
Book D	Y	Y
Book E	Y	Y
Book E	Y	Y
Book F	Y	Y
Book F	Y	Y
Book F	N	Y
Book G	Y	Y
Book G	Y	Y
Book G	N	Y

In above example, we can see that Book A has 1 author and 2 claimants, Book B has 5 authors and 2 claimants, Book C has 1 author and 2 claimants, Book D, and E has 2 authors and 2 claimant and book F and G has 2 authors and 3 claimants. 

I need to create a bar chart to show the counts of books based on the combination of author/claimant . Like total counts of books where there are:

• 1 author / 1 claimant
• 2 authors / 1 claimant
• 3 authors / 1 claimant
• 4 authors / 1 claimant
• 5 authors / 1 claimant

I have created a bar chart using 2 measure which shows all combinations together. 

 To count authors/claimants,  I used expression 

=count({<AUTHOR_FLAG = {'Y'}>} BOOK_NAME) 

=count({<CLAIMANT_FLAG = {'Y'}>} BOOK_NAME)

But how I can group all 1 author / 1 claimant and 2 authors / 1 claimant and so on instead of spreading it out like I have in the bar chart screenshot. 

I am not even sure if it is possible in Bar chart? Any suggestions how I can achieve the desired result. 

[Oleg_Troyansky]

Hi,

Yes, this can be done in a bar chart, with the use of the function AGGR(). 

First, you create a calculated Dimension with the following formula:

AGGR(

count({<AUTHOR_FLAG = {'Y'}>} BOOK_NAME)  & '/' & count({<CLAIMANT_FLAG = {'Y'}>} BOOK_NAME)

, BOOK_NAME)

That will generate all the combinations of # Authors and # Claimants. 

Then, in the measure, you simply count Books with the prefix DISTINCT:

count(DISTINCT BOOK_NAME)

And Voila - you get a bar chart with all the combinations of counts and the count of books.

To learn these advanced techniques, check out my own book QlikView Your Business. Even though it was written for QlikView, all the techniques and methodologies remain true in Qlik Sense.

Cheers,

[pgloc2020]

Thank you so much @Oleg_Troyansky I got the distinct combination of Author and Claimants as follow in a bar chart-

But  Iam trying to see only and only the following combinations and rest counts should display as "Others"

• 1 author / 1 claimant
• 2 authors / 1 claimant
• 3 authors / 1 claimant
• 4 authors / 1 claimant
• 5 authors / 1 claimant

I used the limitation function but still not able to eliminate few combinations like 2/2, 3/3-

Is there a way to show the specific combination in X axis. 

[Oleg_Troyansky · Accepted solution]

Hi,

Yes, you can do that by adding a condition to your calculated dimension formula, something like this:

AGGR(

     IF(  count({<CLAIMANT_FLAG = {'Y'}>} BOOK_NAME)=1 ,

            count({<AUTHOR_FLAG = {'Y'}>} BOOK_NAME)  & '/' & count({<CLAIMANT_FLAG = {'Y'}>} BOOK_NAME)

            , 'OTHERS')

, BOOK_NAME)

Cheers,

[pgloc2020]

Thank you @Oleg_Troyansky It works as I needed. 

Really appreciate your help.