Where to perform calculations ?

[#1]

Hi all,

I am trying to explore Qlikview's capability as a BI tool and trying to go beyond the data visualization that I've been exposed to so far.

I have two tables as shown below

Table A:

Store ID	Guarantee	Rate 1	Rate 2	Rate 3	Sales 1	Sales 2
1	4	5	10	15	100	200
2	5	6	12		100

Table B:

Store ID	Sales
1	150
2	80

I'm trying to calculate the commission for each stores based on it's sales. For example, if store 1 has sales below 100, it gets 5% of sales , if its between 100 to 200 , it gets 10% and if it exceeds 200 , it gets 15%. There's also a guarantee amount for each store. In case of store 2, If it exceeds 100, it gets 12% else 6%.  This is just an example and the source is from an Excel workbook.

My question is: How do I go about this? I have loaded both the tables but not sure how to get data out of both the tables and then calculate the commission.Should I create a new table or create variables or any other way? Can someone help me out here ?

Thanks,

Praveen

[#2]

Any suggestion? I'm not looking for the code , just a way of knowing how to go about.

[Anonymous]

Hi,

You can  try

if(ID='1',if(Sales=100,'5%',if(Sales>100 and Sales<=200,'10%',if(Sales>200,'15%'))),

if(ID=2 and Sales(1)>100,'12%','6%'))

[Anonymous]

where column % is a  expression

[#5]

Thank you for your reply. The example provided here is just a sample and there will be loads of other checks I need to do. My primary quesiton is where do I perform these calculations? I would ideally prefer to do it while loading the script not as an expression inside the charts. Second, How do I get bring data from two different tables and calculate the commission ? Where would I store this variable? Is it in a new table or a global variable? Since I'm new to Qlikview , I'm not sure of its scripting design.