Dynamic Lookup using calculated measure

[#1]

Hi All,

I have a measure defined within the application with set analysis and based on the result (integer) i would like to get a corresponding values which is defined in a data mapping table.

Eg.

Data Mapping Table

LowestValue - HighestValue - Category

0 - 50 - A

51 - 100 - B

101 - 150 - C

151 - 200 - D

so on...

In the qlik sense application I would like to get the following table result where:

customer is a dimension value

value is a calculated measure

Category is a lookup result based on the interval cluster for value and the data mapping table

Customer - Value - Category

Andrea - 45 - A

Kris - 123 - C

Paul - 189 - D

In the above example Andrea's value, 45, is between 0 and 50 which is the A interval defined in the data mapping table.

I'm not able to lookup dynamically based an interval.

Any idea?

Thanks,

Antonello

[sunny_talwar]

So, you don't want to do this in the script? May be using Aggr() function.... but this will take some toll on performance.

[#3]

Actually don't know if it's feasible. This is the expression for the dynamic measure:

sum(

aggr(

( Sum({<LEVEL={'Product'}>}UNIT*(1+Growth)) / Sum({<LEVEL={'Market'}>}UNIT*(1+Growth)) ) 

/

( Sum({<LEVEL={'Product'}>}total UNIT*(1+Growth)) / Sum({<LEVEL={'Market'}>}total UNIT*(1+Growth)) )

*100

*

PRD_WEIGHT

,Submarket,ISF)

)

[sunny_talwar]

It might be difficult to propose much without a sample.... Would you be able to share a sample app and explain what your desired output is?

[#5]

I have attached an example.

The final result I would like to get is one more column which is goign to get corresponding Cluster value based on Area and the interval for the Value expression.

Should be

User	Area	Value	Cluster
Andrea	Custom	17	B
Andrea	Standard	11	A
Frank	Custom	23	B
Frank	Standard	23	A
Patrick	Custom	56	D
Patrick	Standard	45	C
Paul	Custom	87	D
Paul	Standard	67	D

[sunny_talwar · Accepted solution]

Try this:

Aggr(If(Sum({<Year={'2016'}>}Value) > Low and Sum({<Year={'2016'}>}Value) < High and Area = [Source Area], Cluster), User, Cluster, Area, [Source Area])

It might be helpful if you join Area and Source Area field because then you can will be able to just use this

Aggr(If(Sum({<Year={'2016'}>}Value) > Low and Sum({<Year={'2016'}>}Value) < High, Cluster), User, Cluster, Area)

Assuming you rename Source Area to Area

[#7]

Thanks... I will take some time to understand the solution but it works