Comparing values

[Jeremy]

Hi,

This is one of 4 benchmarks loaded from excel file and it is not linked to other tables.

Benchmark	MinValue	MaxValue	Rate
Production	100		10
Production	90	99,99	9
Production	80	89,99	8
Production	70	79,99	7
Production	60	69,99	6
Production	50	59,99	5
Production	40	49,99	4
Production	30	39,99	3
Production	20	29,99	2

Before devoloping app any further, I need some help with set analysis(?).

How to get the rate to straight table without using any selections?

This simple calculations works when using more dimensions but that is not the solution:

if(Sum(ProductionValue)/sum(Hours)>=MinValue and Sum(ProductionValue)/sum(Hours)<=MaxValue, Rate)

Week	Sum(ProductionValue)/sum(Hours)	Rate
33	17,75	-
34	90,70	-

[Jeremy]

Anyone?

[Jeremy]

Some progress...

I got rate 9 for week 34 by using FirstSortedValue. Still have to find out how to get the rate if value is below range.

FirstSortedValue(Rate,
aggr(if(Sum(ProductionValue)/sum(Hours)>=MinValue,-MinValue),Week,Rate))

[Brett_Bleess]

Here is a Design Blog link that may help you further with things:

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

There are some additional/related links at the bottom of that post, just FYI.  

Here is Help link:

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/ChartFunctions/...

This will kick things again, someone else may have some further guidance as well.

Regards,
Brett

[Jeremy · Accepted solution]

Thanks! I haven't seen that link before about set analysis.

Anyway, FirstSortedValue with if, did the trick and app is working.