Skip to main content
Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
cancel
Showing results for
Search instead for
Did you mean:
Creator II

## Complex expression (count if sum...)

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.

1 Solution

Accepted Solutions
MVP

May be this

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

14 Replies
MVP

May be this

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

MVP

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Master

Hi Anton,

Maybe

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

Cheers

Andrew

Creator II
Author

Thanks for trying but unfortunately did not work ...

Creator II
Author

Thank you very much.

MVP

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Creator II
Author

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.

Community Browser