topic Re: Nested expressions in measure in App Development

Nested expressions in measure

pgloc2020 — Tue, 21 Nov 2023 19:57:29 GMT

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.

Re: Nested expressions in measure

Oleg_Troyansky — Wed, 22 Nov 2023 15:02:12 GMT

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,

Re: Nested expressions in measure

pgloc2020 — Wed, 22 Nov 2023 16:05:53 GMT

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.

Re: Nested expressions in measure

Oleg_Troyansky — Wed, 22 Nov 2023 16:25:30 GMT

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,

Re: Nested expressions in measure

pgloc2020 — Wed, 22 Nov 2023 18:34:19 GMT

Thank you @Oleg_Troyansky It works as I needed.

Really appreciate your help.