I'm brand new to QlikView (and discussion groups for that matter). I've had extensive experience with Excel and VBA (which I think is holding me back a bit here, since it's a whole new language).
I'm trying to create a calculated rebate owed to a variety of clients with special contracts. The trick is, each contract has different terms, and only select divisions of our company have to honor the rebate with certain clients accross hundreds of orders. The raw data, stored in an xlsx file, looks somewhat like this:
Client # Contract Division1 Division2 Division3
4637 ABC X X
7072 ABC X X
8496 ABC X
3995 DEF X X X
4278 DEF X X
4728 XYZ X X
9278 XYZ X X X
It would read something like this: "Client 4637 is subject to rebate ABC when doing business with Divisions 1 & 3, but not when doing business with Division 2.", etc.
A second table shows the rebate terms for each contract:
I have another table, loaded from out General Ledger, showing division, client number, order number, and invoice amounts.
The idea here is to generate a table that will return the rebate due for each order on each contract. I used to have to do this with an Excel workbook using multiple conditional vlookup functions and a pivot table. If I can get the right output in a QlikView table, the same rebate can be calculated and exported to a spreadsheet in a fraction of the time.
The Division and Contract fields are in list boxes on the main tab. I made a pivot table with the Client #, and Order # in the dimensions, and a calculated expression "Rebate Amount", which looks like this:
If((Division=1 and Division1='X') or (Division=2 and Division2='X') or (Division=3 and Division3='X'), Sum(InvoiceAmount) * RebatePct, 0)
From what I can tell, the calculation is coming through correctly, but I'm also getting several lines in the pivot table with zero values in the Rebate Amount (for clients not subject to any of the contracts, or for client orders with divisions not governed by the contract - as in Division 2 for Client 4637).
I'm not opposed to approaching this from a completely different angle if there's a better way to get at it. I've just gone at it the best way I could come up with given my background, but I'm definately open to learning new skills in QlikView.