Qlik Community

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: 
antonaks
Creator II
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_DOCID_SKUSumSaleFlag_1
11120
12250
13310
14460
233301
233321
21190
32270
344190
37730
388230
444431
43391
55520
51110
52270
52230

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
sunny_talwar

May be this

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

View solution in original post

14 Replies
sunny_talwar

May be this

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

sunny_talwar

Sample attached

Capture.PNG

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


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

Hi Anton,

Maybe

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

Cheers

Andrew

antonaks
Creator II
Creator II
Author

Thanks for trying but unfortunately did not work ...

antonaks
Creator II
Creator II
Author

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

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)
antonaks
Creator II
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.