Retrieve value based on region, permission and user selection

[alexlinkreit17]

Hi all,

I have 2 set of data, one is region with user permission, another one is KPI per region / country / city

Hierarchy for location:

+-- North America

| +-- USA

| | +-- New York

| | +-- Boston

| +-- Canada

| | +-- Vancouver

| | +-- Toronto

Permission

NTNAME	ROLE
MA1	*
MA2	New York
MA2	Boston
MA2	Toronto
MA3	Vancouver

KPI

LV	KPI
New York	1.5
Boston	1.2
USA	1.8
Vancouver	1.3
Toronto	1.7
Canada	1.6
North America	2.0

I want to retrieve the KPI based on the user selection and their permission, which is

MA1

     - When select nothing: KPI = 2.0

     - When select USA: KPI = 1.8

     - When select Boston: KPI = 1.2

     - When select both Boston and New York: KPI = 1.8 (USA)

MA2

     - When select nothing: KPI = 1.8 (USA, second level of hierarchy)

MA3

     - When select nothing: KPI = 1.3 (Vancouver, third level of hierarchy)

Is it possible to do in QlikView?

Thanks in advance