Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
March 11, 2PM EST: Do More with Qlik - Getting Started wtih Qlik Sense SaaS - Redux. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
prat1507
Specialist
Specialist

Lookup from a table with range

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 MinRange MaxValue
01100
1101301.2
1301501.4
1501000000001.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

Labels (1)
1 Solution

Accepted Solutions
sunny_talwar

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

image.png

View solution in original post

9 Replies
jwjackso
Specialist II
Specialist II

I think the IntervalMatch function can help.

prat1507
Specialist
Specialist

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


Regards

Pratyush

prat1507
Specialist
Specialist

XYZ.png

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

prat1507
Specialist
Specialist

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


Regards

Pratyush

Steven_Haught
Creator III
Creator III

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
Specialist
Specialist

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

prat1507
Specialist
Specialist

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
Specialist
Specialist

Please help. My customers need this today 😞

sunny_talwar

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

image.png

View solution in original post