cancel
Showing results for
Did you mean:
Contributor III

## Problem AGGR with condition

Hi,

I have a Problem with a 'aggr' formula.

I want to count all the customer with sales > 0.

In my example this will be 75 (count({< CustID = {"=sum(Sales) > 0"}>}CustID)) for store 0592.

All customers are 78  (Count CUSTID)) for 0592

(as you can see in the pic & Excel-File).

Now I want a list of all my stores (counted by store and market).

When I selected the store 0592 I will get 75.

aggr( count( {< CUSTID= {"=sum(Sales) > 0"}>}CustID),  Store, Market )

But if de-select the store and all my stores are shown, I will get the result 78 for the store 0592.

What I need to change that even in that overall list it will be 75?

Can someone pls. help me?

Best Regards

Markus

1 Solution

Accepted Solutions
Specialist

I think I figured out the issue, Markus. Try this expression

Sum(If(Aggr(sum(Sales), Market, Store, [Cust ID] ) > 0, 1))

7 Replies

Hi,

Try normal expression which you used earlier. i.e.

(count({< CustID = {"=sum(Sales) > 0"}>}CustID))

Just add Market as dimension along with Store.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Contributor III
Author

Hi Kaushik,

no Change.

Markus

Hi,

Can you share full data. (I am unable to replicate the issue with given data)

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Specialist

Hi, Markus. I think there must be something else, I made a sample with your data and expressions... and got no error

Contributor III
Author

Hi Andrey,

I found the the Problem.

As you can see there is 1 cutomer who got sales in 2 stores (1 is negative and 1 is positive).

I need to know if the customer in one store is >0 (not in General).

The last column /formula in the 2nd row is not correct.

Can anyone help me with that?

aggr( count({< [Cust ID] = {"=sum(Sales)>0"}>}[Cust ID]),  Market, Store)

Best Regards

Markus

Specialist

I think I figured out the issue, Markus. Try this expression

Sum(If(Aggr(sum(Sales), Market, Store, [Cust ID] ) > 0, 1))

Contributor III
Author

Hi Andrey,

Yes. Perfect. Thank you very much.

Regards

Markus

Community Browser