topic Set Analysis in QlikView

Set Analysis

robertpryde — Sat, 16 Nov 2024 02:17:51 GMT

I have tried no less than 50 different set analysis formula and as yet, have no solution. below is a subset of data as a sample. I would like to count the distinct number of assets where the sum of the station.starved is greater than the sum of the station.blocked.

The end result should be equal to 4.

asset.id	station.blocked	station.starved
asset.one	7595	23557
asset.one	8250	18234
asset.one	14615	11925
asset.one	14985	267
asset.two	275	2
asset.two	393	43
asset.two	644	1462
asset.two	6069	6904
asset.two	6845	3229
asset.three	2275	22581
asset.three	5211	11168
asset.three	5407	7733
asset.three	5715	10076
asset.three	5880	8513
asset.four	2329	19718
asset.four	2844	15774
asset.four	4381	21037
asset.four	10725	3772
asset.four	27784	3758
asset.five	480	15499
asset.five	925	29314
asset.five	1431	18936
asset.five	1673	30824
asset.five	1944	33427

Any ideas would be greatly appreciated

Re: Set Analysis

Frank_Hartmann — Sun, 06 Oct 2019 15:40:54 GMT

Maybe something like this:

=count(DISTINCT {<station.starved={"=aggr(sum(station.starved),asset.id) > aggr(sum(station.blocked),asset.id)"}>}asset.id)

Re: Set Analysis

JGMDataAnalysis — Sun, 06 Oct 2019 16:47:10 GMT

Another interpretation of the problem ...

=Count({<[asset.id] = {"=Sum([station.starved]) > Sum([station.blocked])"}>} DISTINCT [asset.id])

Re: Set Analysis

Brett_Bleess — Mon, 14 Oct 2019 13:34:33 GMT

Robert, did either of the posts resolve your request? If so, please be sure to come back and use the Accept as Solution button to mark the post(s) that helped. If you did something different, consider posting that and mark it, and if you are still working on things, leave an update as to where you stand.

The best I have to try to give you some further resources are the following:

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

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

If you want to search Design Blog area yourself, use the following link:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett