topic Re: Complex expression (count if sum...) in QlikView

Complex expression (count if sum...)

antonaks — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Complex expression (count if sum...)

sunny_talwar — Thu, 19 Oct 2017 12:46:33 GMT

May be this

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

Re: Complex expression (count if sum...)

sunny_talwar — Thu, 19 Oct 2017 12:48:39 GMT

Sample attached

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

Re: Complex expression (count if sum...)

Anil_Babu_Samineni — Thu, 19 Oct 2017 12:51:05 GMT

May be this?

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

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

Re: Complex expression (count if sum...)

effinty2112 — Thu, 19 Oct 2017 12:53:33 GMT

Hi Anton,

Maybe

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

Cheers

Andrew

Re: Complex expression (count if sum...)

antonaks — Thu, 19 Oct 2017 13:16:06 GMT

Thanks for trying but unfortunately did not work ...

Re: Complex expression (count if sum...)

antonaks — Thu, 19 Oct 2017 13:18:40 GMT

Thank you very much.

Re: Complex expression (count if sum...)

sunny_talwar — Thu, 19 Oct 2017 13:19:45 GMT

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

Re: Complex expression (count if sum...)

Anil_Babu_Samineni — Thu, 19 Oct 2017 13:28:47 GMT

Just to enthusiastic, Can you try this?

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

Re: Complex expression (count if sum...)

antonaks — Thu, 19 Oct 2017 13:32:58 GMT

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.

Re: Complex expression (count if sum...)

sunny_talwar — Thu, 19 Oct 2017 13:38:04 GMT

Set analysis is the way to go here

Re: Complex expression (count if sum...)

antonaks — Thu, 19 Oct 2017 14:07:00 GMT

I can not implement it in the application.
The formula looks like this:

Count (DISTINCT {<DOC_FILIAL_KEY = {"= Sum ({$ <[CHANNEL NAME] - = {'Manufacturer'}, [DOCUMENT TYPE ID] = {2,7,10}, [CODE TYPE] = {1,2 , 4,15,19,20,27,28,29,30}, CM_TD = {1}>} ([AMOUNT WITH VAT] * [OPERATION TYPE])> 50 "} >} DOC_FILIAL_KEY)

Re: Complex expression (count if sum...)

sunny_talwar — Thu, 19 Oct 2017 14:14:05 GMT

Try now:

Count (DISTINCT {<DOC_FILIAL_KEY = {"=Sum({$<[CHANNEL NAME] -= {'Manufacturer'}, [DOCUMENT TYPE ID] = {2,7,10}, [CODE TYPE] = {1,2 , 4,15,19,20,27,28,29,30}, CM_TD = {1}>} ([AMOUNT WITH VAT] * [OPERATION TYPE])) > 50"}>} DOC_FILIAL_KEY)

Re: Complex expression (count if sum...)

antonaks — Thu, 19 Oct 2017 14:20:40 GMT

I missed the bracket
Works 100 times faster !!!
Thank you very much!

Re: Complex expression (count if sum...)

sunny_talwar — Thu, 19 Oct 2017 14:29:44 GMT

Awesome