Announcements
Sept. 27, 10AM ET Do more with Qlik, Insight Advisor – our intelligent AI-assistant in Qlik Sense: Register
cancel
Showing results for
Did you mean: Not applicable

## Dynamic Lookup using calculated measure

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

1 Solution

Accepted Solutions  MVP

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

6 Replies  MVP

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

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)

)  MVP

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? Not applicable
Author

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  MVP

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 Not applicable
Author

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