Complex expression (count if sum...)

[antonaks]

Hello dear community!
I have a problem with a complex expression.

It is necessary to count the number of documents in which the amount of sales is more than a certain number (> 50), as well as with the filter in SetAnalysis.

The difficulty is that the document in my array is not aggregated by unique.

As an example, the data looks like this:

ID_DOC	ID_SKU	SumSale	Flag_1
1	11	2	0
1	22	5	0
1	33	1	0
1	44	6	0
2	33	30	1
2	33	32	1
2	11	9	0
3	22	7	0
3	44	19	0
3	77	3	0
3	88	23	0
4	44	43	1
4	33	9	1
5	55	2	0
5	11	1	0
5	22	7	0
5	22	3	0

The solution I imagine is like this:

Count(DISTINCT ID_DOC)

where

Aggr(Sum(SumSale),ID_DOC) > 50

and

Flag_1=1

The result of the decision needs to be the number - 2, if we take the above example data.

Please help.

With respect Anton.

[sunny_talwar]

Set analysis is the way to go here

[antonaks]

I can not implement it in the application.
The formula looks like this:

Count (DISTINCT {<DOC_FILIAL_KEY = {"= Sum ({$ <[CHANNEL NAME] - = {'Manufacturer'}, [DOCUMENT TYPE ID] = {2,7,10}, [CODE TYPE] = {1,2 , 4,15,19,20,27,28,29,30}, CM_TD = {1}>} ([AMOUNT WITH VAT] * [OPERATION TYPE])> 50 "} >} DOC_FILIAL_KEY)

[sunny_talwar]

Try now:

Count (DISTINCT {<DOC_FILIAL_KEY = {"=Sum({$<[CHANNEL NAME] -= {'Manufacturer'}, [DOCUMENT TYPE ID] = {2,7,10}, [CODE TYPE] = {1,2 , 4,15,19,20,27,28,29,30}, CM_TD = {1}>} ([AMOUNT WITH VAT] * [OPERATION TYPE])) > 50"}>} DOC_FILIAL_KEY)

[antonaks]

I missed the bracket
Works 100 times faster !!!
Thank you very much!

[sunny_talwar]

Awesome