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 · Accepted solution]

May be this

Count(DISTINCT {<ID_DOC = {"=Sum({<Flag_1 = {1}>}SumSale) > 50"}>} ID_DOC)

[sunny_talwar]

Sample attached

Top text box shows the count and second box shows which ID_DOCs meet the condition

[Anil_Babu_Samineni]

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))

[effinty2112]

Hi Anton,

Maybe

=Count(Aggr(if(Sum({$<Flag_1 = {1}>}SumSale)>50,ID_DOC),ID_DOC))

Cheers

Andrew

[antonaks]

Thanks for trying but unfortunately did not work ...

[antonaks]

Thank you very much.

[sunny_talwar]

It seems like you are not going with the set analysis option, is there a reason why you choose not to use this?

[Anil_Babu_Samineni]

Just to enthusiastic, Can you try this?

Count( Aggr(sum( {< SumSale = {"=Sum({<Flag_1 ={1} >} SumSale) > 50"}>}SumSale),ID_DOC))

[antonaks]

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.