Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Markus_QV
Contributor III
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
andrey_krylov
Specialist
Specialist

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

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

View solution in original post

7 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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!
Markus_QV
Contributor III
Contributor III
Author

Hi Kaushik,

no Change.

Regrads

Markus

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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!
andrey_krylov
Specialist
Specialist

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

Markus_QV
Contributor III
Contributor III
Author

Hi Andrey,

I found the the Problem.

Problem_Cust_ID.jpg

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

andrey_krylov
Specialist
Specialist

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

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

Markus_QV
Contributor III
Contributor III
Author

Hi Andrey,

Yes. Perfect. Thank you very much.

Regards

Markus