Count problem

[darroyo_telcomu]

Hello,

first of all I apologize because I can't upload an example app of this matter. So I'm going to write a paralel example of my problem.

Imagine a table with the following fields:

MONTH: month when a person has travelled

PERSON: person who has travelled

TRAVELTYPE: business/pleasure

Persons can travel each month for business, for pleasure, both, or no travel. So records would seem like that:

JANUARY - PERSON1 - BUSINESS

JANUARY - PERSON1 - PLEASURE

JANUARY - PERSON2 - BUSINESS

FEBRUARY - PERSON1 BUSINESS

FEBRUARY - PERSON2 - PLEASURE

FEBRUARY - PERSON3 - BUSINESS

...

I need to configure a pie chart showing persons that never has travelled for pleasure. In my example data would be a pie chart showing 1 person with no pleasure travel (PERSON3) and 2 persons with some pleasure travel (PERSON1 and PERSON2).

I've been trying with a "PleasureTravelCounter" 0/1, but I don't get it.

Any help in spite of my explanation?

Best regards,

David.

[Gysbert_Wassenaar]

Try a pie chart with two expressions:

No pleasure: Count({<PERSON=E({<TRAVELTYPE={'PLEASURE'}>}PERSON)>} distinct PERSON)

Pleasure Count({<TRAVELTYPE={'PLEASURE'}>} distinct PERSON)

[darroyo_telcomu]

Thank you gwassenaar! I didn't know E() and P() functions and they seem to be very useful

I think we almost get it. My pie has a dimension "Pleasure" / "Not pleasure" because the table records have a flag as follows:

JANUARY - PERSON1 - BUSINESS - "No pleasure"

JANUARY - PERSON1 - PLEASURE - "Pleasure"

JANUARY - PERSON2 - BUSINESS - "No pleasure"

FEBRUARY - PERSON1 BUSINESS - "No pleasure"

FEBRUARY - PERSON2 - PLEASURE - "Pleasure"

FEBRUARY - PERSON3 - BUSINESS - "No pleasure"

If I add a dimension with your code:
Count({<PERSON=E({<TRAVELTYPE={'PLEASURE'}>}PERSON)>} distinct PERSON)

then the full pie is showing correctly how many people has not travelled for pleasure, that's works fine! But it fills the whole pie and I don't know how to show the other pie section with persons that have travelled for pleasure.

I am use to working with one dimension and one measure in pies, so I don't know how to manage with both expressions you give me (the one for pleasure and the one for no pleasure).

Thanks again for your support.

Best regards,

David.

[Gysbert_Wassenaar]

Don't use any dimensions and the two expressions I posted above.

[darroyo_telcomu]

If I don't use any dimension the following message appears: "Incomplete visualization".

I've tried with putting "No pleasure" in one measure and "Pleasure" in the alternative measure. I'm working with Qlik Sense 3.

[Gysbert_Wassenaar · Accepted solution]

Ah, right. Qlik Sense. My mistake.

Use a synthetic dimension: =valuelist('Pleasure','No Pleasure')

And one expression:

=If(valuelist('Pleasure','No Pleasure')='Pleasure',

     Count({<TRAVELTYPE={'PLEASURE'}>} distinct PERSON),

     Count({<PERSON=E({<TRAVELTYPE={'PLEASURE'}>}PERSON)>} distinct PERSON)

)

[darroyo_telcomu]

Perfect! This kind of solution with synthetic dimension looks great!

Thank you gwassenaar‌

[darroyo_telcomu]

Ooops, the pie chart is OK but it doesn't work as a filter. I mean I can't click in une pie section and filter that persona that has not travelled by pleasure. Can this be solved?