count(sum<set-expression> in condition)

[jakobjensen]

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)

[agigliotti]

maybe this:

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

I hope it can helps.

[jakobjensen]

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"} " ?

[Brett_Bleess]

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/...

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