Lookup from a table with range

[prat1507]

HI All

I've a table where I'm using a measure to calculate the average and if conditions like:

if(Ind = 'L',Sum(Sales))/Sum(minutes)  and this is computed across a table for each employee and his dept.

Based on this value, I need to provide an incentive, hence I've a range of incentives like

Range Min	Range Max	Value
0	110	0
110	130	1.2
130	150	1.4
150	100000000	1.8

If the computed value falls in the given range, I need to add a measure to the table stating the incentive the employee would get.

Thanks in advance!

Regards

Pratyush

[jwjackso]

I think the IntervalMatch function can help.

[prat1507]

The measure is computed at the visualization level, I don't want to do it in the load script.

Regards

Pratyush

[prat1507]

 Please findd the attached app. I've selected KGI and CFS as e-code and Week 30/2020.

[prat1507]

Can anyone please look into this and help. I'm willing to clarify if there are any doubts or concerns.

Regards

Pratyush

[Steven_Haught]

Try something like this. 

Add the following as inline table in your load to work as the table to look up value:

load * Inline [
category, range, score
a, 150, 1.8
b, 130, 1.4
c, 110,1.2
d, 0, 0
] ;

Then you will need to add the following as a measure in your table:

IF(YOURAGGREGATEEXPRESSION >=
Only({1<category={'a'}>}range),
Only({1<category={'a'}>}score),
IF(YOURAGGREGATEEXPRESSION >=
Only({1<category={'b'}>}range),
Only({1<category={'b'}>}score),
If(YOURAGGREGATEEXPRESSION >=
Only({1<category={'c'}>}range),
Only({1<category={'c'}>}score),
IF(YOURAGGREGATEEXPRESSION >=
Only({1<category={'d'}>}range),
Only({1<category={'d'}>}score)
))))

[prat1507]

What if the customer increases 1000s of range tomorrow. This solution would not work since it is not dynamic.

[prat1507]

Hello everyone

I would really appreciate if someone can help on this. I've tried almost everything and really need your expertise here

@sunny_talwar  I know you can solve this 🙂

Regards

Pratyush

[prat1507]

Please help. My customers need this today 😞

[sunny_talwar · Accepted solution]

May be something like this

Aggr(
	If(Dept = [dept-desc] and (Sum({<[pk-ind] = {'I'}>}[o-sentqty])/(Sum(Minutes)/60)) >= Only({<Task={"Cases Assembled"}>}[Range Min]) and (Sum({<[pk-ind] = {'I'}>}[o-sentqty])/(Sum(Minutes)/60)) <= Only({<Task={"Cases Assembled"}>}[Range Max]), Only({<Task={"Cases Assembled"}>} Value))
, Dept, [Range Min], [Range Max], [e-code], [e-name], [pk-ind], [Assemblydate.autoCalendar.Week], [dept-desc])

The example you gave was not workable because the dept-desc was Grocery on them and I didn't see any Dept Grocery. There were only Frozen and Non-Frozen. I picked another example to test the data