topic count(sum<set-expression> in condition) in QlikView

count(sum in condition)

jakobjensen — Thu, 27 Aug 2020 08:54:02 GMT

I have two columns "value" and "ID".

I want to count, how many IDs have their sum of value either greater than 100 or between 100 and 200 ie.

sum(value)<100

and

100<=sum(value)<200

I tried

count(sum({<sum(value)={"<100"}>}) distinct id)

but you cannot nest aggreation.

Generally; how is a function applied to a set expression? I

(TBH, I think Qlik Sense documentation is extremely bad for newcommers)

Re: count(sum in condition)

agigliotti — Thu, 27 Aug 2020 09:39:55 GMT

maybe this:

count( {< id = {"=sum(value)<100"} >} distinct id )

I hope it can helps.

Re: count(sum in condition)

jakobjensen — Thu, 27 Aug 2020 09:47:53 GMT

I'll give it a shot.

Where can I find more about how you created the "=sum(value)<100" part? It is not mentioned in the set expression part in the documentation.

Furthermore, how is that understood? Are we creating a new variable called "id" or should I understand the

id = {"=sum(value)<100"} >" part as "give me id who fullfill these requriements"? and in that case, could I have multiple returns, i.e say I also had a country like "id,country = {"=sum(value)<100"} " ?

Re: count(sum in condition)

Brett_Bleess — Tue, 08 Sep 2020 20:43:14 GMT

See the following Design Blog posts, hopefully they may shed some further light upon things, and including Help link below that, but it seems you had looked at that, but I wanted to be sure.

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

https://community.qlik.com/t5/Qlik-Design-Blog/A-Primer-on-Set-Analysis/ba-p/1468344

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/ChartFunctions/SetAnalysis/set-analysis-expressions.htm

Two sub areas under the Help link, just FYI, be sure you review those too, that is likely where your missing info may be.

Regards,
Brett