topic Re: Count Distinct based on positive sum - Expression in App Development

Count Distinct based on positive sum - Expression

Fri, 12 May 2017 06:30:18 GMT

Hello,

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

There are 3 parameters in my table:

Buyer	Šifra artikla	Neto
A	123 4	200
A	123 4	-200
A	567 8	300
B	567 8	200
B	123 4	100

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

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

Buyer	Count
A	2
B	2

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:

Buyer	Count
A	1
B	2

Help appreciated.

Thank you.

Re: Count Distinct based on positive sum - Expression

antoniotiman — Fri, 12 May 2017 07:33:21 GMT

Hi Kristian,

try

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

Regards,

Antonio

Re: Count Distinct based on positive sum - Expression

Fri, 12 May 2017 07:41:42 GMT

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

Re: Count Distinct based on positive sum - Expression

Fri, 12 May 2017 07:48:46 GMT

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

Re: Count Distinct based on positive sum - Expression

Fri, 12 May 2017 08:11:44 GMT

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

Re: Count Distinct based on positive sum - Expression

Fri, 12 May 2017 08:36:33 GMT

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):

Correct amounts (month by month)

I appreciate your help, thank you

Re: Count Distinct based on positive sum - Expression

antoniotiman — Fri, 12 May 2017 08:42:26 GMT

Try to add FaktureMonth to Aggr()

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

Re: Count Distinct based on positive sum - Expression

Fri, 12 May 2017 09:05:52 GMT

Thank you. That helped.