Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Set analysis is the way to go here
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)
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)
I missed the bracket
Works 100 times faster !!!
Thank you very much!
Awesome