Qlik Community

Qlik Sense App Development

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

Not applicable

Count Distinct based on positive sum - Expression

Hello,

i've got a small problem and i am hoping somebody can help me.

There are 3 parameters in my table:

BuyerŠifra artiklaNeto
A123 4200
A123 4-200
A567 8300
B567 8200
B123 4100

This is my formula: count(DISTINCT{<NETO = {">0"}>}[Šifra artikla])

Basically the formula Counts the "Šifra artikla" where Neto is >0 per Buyer.

BuyerCount
A2
B2

Now the problem is, i don't want that. I want the formula to count where sum of neto per "Šifra artikla" is >0, so that the final result is:

BuyerCount
A1
B2

Help appreciated.

Thank you.

1 Solution

Accepted Solutions
antoniotiman
Honored Contributor III

Re: Count Distinct based on positive sum - Expression

Try to add FaktureMonth to Aggr()

Count(DISTINCT Aggr(If(Sum(NETO) > 0,[Šifra artikla]),Komercijalist,FaktureMonth,[Šifra artikla]))

7 Replies
antoniotiman
Honored Contributor III

Re: Count Distinct based on positive sum - Expression

Hi Kristian,

try

Count(DISTINCT Aggr(If(Sum(Neto) > 0,Sifra),Buyer,Sifra))

Regards,

Antonio

Not applicable

Re: Count Distinct based on positive sum - Expression

Unfortunately not. "Buyer" is a Dimension defined in Chart properties, so the result in your formula shows me 0

Not applicable

Re: Count Distinct based on positive sum - Expression

NVM found the solution, i looked at the wrong stuff... here is the "working" formula:

"count({<[Šifra artikla] = {"=sum(NETO) > 0"}>} DISTINCT [Šifra artikla])"

Thank you

Not applicable

Re: Count Distinct based on positive sum - Expression

Nope... i was wrong my formula ain't right

Not applicable

Re: Count Distinct based on positive sum - Expression

My Bad... i didn't give enough information about the table. Your Formula is working with the information given...

Unfortunately i forgot to mention that my table has a lot more fields like month, year...

So when i make a Chart with -

Dimensions: Buyer, FaktureMonth

Expressions: Count(DISTINCT Aggr(If(Sum(NETO) > 0,[Šifra artikla]),Komercijalist,[Šifra artikla]))


As long i select(Filter) only one month to display, your Formula works and gives me the correct amount. But if i select more months the number changes and is now wrong. Can you adjust the formula to work with filtered dimensions?

wrong amounts(4 months selected):

Display with more months.JPG

Correct amounts (month by month)

Display january.JPG

Display February.JPG

I appreciate your help, thank you

antoniotiman
Honored Contributor III

Re: Count Distinct based on positive sum - Expression

Try to add FaktureMonth to Aggr()

Count(DISTINCT Aggr(If(Sum(NETO) > 0,[Šifra artikla]),Komercijalist,FaktureMonth,[Šifra artikla]))

Not applicable

Re: Count Distinct based on positive sum - Expression

Thank you. That helped.