Count either match() or substring() dependant on distinct ID

[gorkemakinci]

Hi there,

I'm trying to create a pie chart to be able to visualise the ratios of 3 different values of a field. And for that I'm using a simple dimension expression as;

=(if(SubStringCount([Example],'A')>0,'A', 
if(SubStringCount([Example],'B')>0,'B', 
if(SubStringCount([Example],'C')>0,'C', Null()))))

and my measure for their ratios is as following;

Count((if(SubStringCount([Example],'A')>0,'A', 
if(SubStringCount([Example],'B')>0,'B', 
if(SubStringCount([Example],'C')>0,'C', Null())))))

If I only had one ID this would've worked like a charm, but I have one ID field and a subID field, so basically my data is like a tree but this [Example] field is auto filled before I load it to Qlik Sense. So to sum it up, for example I have an ID as "1" and an ID as "2", ID "1" has 4 subID's so it has 4 values of A for example. And ID "2" has 2 subID's and therefore it has 2 C values.

Right now my expression is giving me 4 A values and 2 C values therefore I can't acquire the correct ratio for 2 ID's as a result of that auto filling process. I Hope I was able to explain it well enough.

P.S. I am aware that I could just load a new table for frequenct of these values based on the main ID with a Load Distinct in the load editor, and then use those fields for the pie chart, but I really would love to handle this inside the expressions and not from the load editor.

Thanks in advance, have a nice one everyone!

[MayilVahanan]

Hi

Try like below

Exp: Count(Distinct Example)

In Dimension, its already restricted to display only A, B & C. 

[gorkemakinci]

Hi again,

I couldn't explain it properly so I'll write down an example input and a desired outcome.

ID	SubID	Example
1	x	A
1	y	A
1	z	A
2	x	B
2	y	B
3	x	C
3	y	C
3	z	C
4	x	B
4	y	B

So from this table example I don't want distinct values on Example field, I want the values of Example fields for distinct ID's, but was unable to write the expression in the pie chart.

Desired Outcome:

ID	SubID	Example
1	doesn't matter at all	A
2	doesn't matter at all	B
3	doesn't matter at all	C
4	doesn't matter at all	B

Sorry if I was unclear from the beginning of the topic. Have a nice one!

[MayilVahanan]

Sorry ya, so u are expecting the Example B has 2 Id in Pie Chart?

If so , try like below

Dim: Example

Exp:Count(Distinct ID)

Else, can you show the expected output?

[gorkemakinci]

Yes that was just a small scale example, so when I try it like this I'm getting the error "The chart is not displayed because it contains only undefined values."

It is because I actually have a concatenated column instead of the Example column like I wanted, that's why I'm dealing with substring functions in my example code.

Is there any possible way to insert our limitation into the code I shared in the first post, somethink like group by() or distinct in the correct position? I tried it but wasn't successful so maybe you can think of something else?

[marcus_sommer]

I think it could be done much simpler by skipping the dimension and just applying 3 expressions, like:

count({< Example = {"*A*"}>} Example)
count({< Example = {"*B*"}>} Example)
count({< Example = {"*C*"}>} Example)

with absolute results and each one dividing per:

count({< Example = {"*A*", "*B*", "*C*"}>} Example)

would return the rates.

[gorkemakinci]

Can't try it atm as I'm not on my PC, but I wanted to ask do you think would it work if the Example field has more than couple of values inside it? Because the reason I used Substring Count is that there are values as "A A B B B C D E F" and I only am interested in A, B and C for example. I also want 2 A's to be counted from this example value and 3 B's and 1 C. Don't know if this makes sense for you but I'll let you know once I'm able to try it out!

[marcus_sommer]

In the provide logic above it's regardless how many chars of A, B or C are there - it will return TRUE if at least one is found and would not be different from your substringcount() > 0. A complex counting wouldn't be possible with the above simple set analysis whereby a few variances might be also practicably possible, like:

count({< Example = {"*A*A*A*"}> + < Example = {"*A*A*"}>} Example)