# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming Aug. 9: New Simplified Authoring for Qlik Sense SaaS – For Details, CLICK HERE
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))

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
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))

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
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.