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.
May be this
Count(DISTINCT {<ID_DOC = {"=Sum({<Flag_1 = {1}>}SumSale) > 50"}>} ID_DOC)
May be this
Count(DISTINCT {<ID_DOC = {"=Sum({<Flag_1 = {1}>}SumSale) > 50"}>} ID_DOC)
Sample attached
Top text box shows the count and second box shows which ID_DOCs meet the condition
May be this?
Count( {< Flag_1 ={1} >} aggr(sum( {< SumSale = {"=Sum(SumSale) > 50"}>} SumSale),ID_DOC))
OR
Count( {< Flag_1 ={1} >} aggr(sum( {< Flag_1 ={1}, SumSale = {"=Sum(SumSale) > 50"}>} SumSale),ID_DOC))
Hi Anton,
Maybe
=Count(Aggr(if(Sum({$<Flag_1 = {1}>}SumSale)>50,ID_DOC),ID_DOC))
Cheers
Andrew
Thanks for trying but unfortunately did not work ...
Thank you very much.
It seems like you are not going with the set analysis option, is there a reason why you choose not to use this?
Just to enthusiastic, Can you try this?
Count( Aggr(sum( {< SumSale = {"=Sum({<Flag_1 ={1} >} SumSale) > 50"}>}SumSale),ID_DOC))
It seems to me that the absence of Aggr () can lead to an error. But I already see that without SetAnalysis, the calculation takes a very long time. My fact table has a size of more than 100,000,000 rows.
Now I'll try your option.