Identify a value for a metric based on the range from a table/ score the metric based on ranges defined in a table.

[seemawadekar]

Hi,

I am trying to identify a value that needs to go into KPI based on range of values of a metric (stored in a table) on the sheet (should be able to aggregate or slice based on filter selections) 

On the sheet, I am calculating the master measure for each metric, then it needs to identify which range it falls under and then assign the points associated to that range to a KPI.  Each metric has multiple ranges. 

The criteria table looks like this

Any pointer?

Thank you. 

[Aditya_Chitale]

Can you share sample qvf along with your desired output ?

Regards,

Aditya

[henrikalmen]

Take a look att the IntervalMatch() function, perhaps it will help you do what you want to achieve. https://help.qlik.com/en-US/sense/November2022/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPref...

[seemawadekar]

Hi, Intervalmacth() works in load script only. I need to do this on the sheet. 

[seemawadekar]

Unfortunately not due to company policies. 

[henrikalmen]

Are you saying that you are calculating a number, and you need to know what points that result should be assigned based on the criteria table?

Maybe you should try to create an expanded criteria table in load script, that looks something like this:

Metric Name	RangeIndex	Points
Metric1	1	1
Metric1	2	1
Metric1	3	1
...	...	...
Metric1	10	1
Metric1	11	5
Metric1	12	5

...and so on.

Then you could check the result of your calculation against the RangeIndex column in the expanded criteria table and extract the Points value from there.

[seemawadekar]

Thanks @henrikalmen  Will try that and let everyone know. 

[seemawadekar · Accepted solution]

Thank you all for your responses. 

I ended up calculating the values in load script, then Join it with the metric table and then using MAX(points ) on the chart.