Supressing zero values in a calculated expression in a pivot table

[#1]

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:

Contract RebatePct
ABC 2.0%
DEF 1.5%
XYZ 2.3%

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).

Any suggestions?

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.

[johnw]

If all you're trying to do is suppress zero values in the pivot table, you should be able to do that in the chart properties, presentation tab. There's a checkbox to suppress zero values. That should be the default behavior, so I'm not sure why you'd be seeing zero values in the first place.

But I'd handle things differently regardless. I'd eliminate fields Division1, Division2 and Division3. I'd flatten out the divisions with a crosstable load to get this:

Client # Contract Division
4637 ABC 1
4637 ABC 3
7072 ABC 1
7072 ABC 3
etc.

Heck, I might even left join your rebate percentages onto that table to simplify the data model a bit. Denormalization isn't the no-no in QlikView that it is in some realms. But it isn't necessary here either. Then change your expression to:

sum(InvoiceAmount) * only(RebatePct)

Seems like it would be much simpler overall. If you can't figure out the crosstable load, and want to post some sample data that includes some orders, I can try to work it out.

[#3]

Thanks for taking a few minutes to help me out.

I tried the supress zero setting on the Presentation tab with no change in the table layout.

** I finally realized what I did (rookie mistake). I had other calculated expressions in the pivot table, which over-ride the supression on the rebate amount field. Along those lines, is there a way to force QlikView to supress according to values returned in one column of a pivot table (akin to the Excel "Auto Filter" feature)?

As for your crosstable load suggestion, the actual client list is 2700 lines by 12 divisions. It would take a great deal of time to manually re-structure the data. I could probably write a VBA macro script that will re-build the list in the layout you've suggested. Is there a way of manipulating the QlikView load script to do it?

[johnw]

In answer to your first question, let's say the expression you want suppressed when zero is name "Expression A". You could change your other expressions to this structure:

if("Expression A",some other expression here)

They'll then be null when Expression A is zero (0=false), and therefore the whole row should be suppressed. You're not repeating the expression in the if(), just using the name of the expression. There are also ways to do the same thing by using a calculated dimension, but to me it's less clear and appears to execute much more slowly, so I'd only use it if I had enough expressions to get really annoying. I'd generally use something like the above approach.

In regards to the crosstable load, yes, QlikView can do the restructuring for you. Look up crosstable loads in the help text. I thought for sure I'd have an example, but the only two example files I have with crosstable loads appear to have the crosstable load as an incidental piece, and are mostly demonstrating something else, so they're not very good. Hmmmm... well, I guess I'll do an example with your data then. See attached. Script below.

CROSSTABLE ("Division","Keep if X",2)
LOAD
"Client #"
,"Contract"
,"Division1" as "1"
,"Division2" as "2"
,"Division3" as "3"
INLINE [
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
];
INNER JOIN
LOAD 'X' as "Keep if X"
AUTOGENERATE 1
;
DROP FIELD "Keep if X";

[#5]

Thanks a million for the pointers. The conditional expression worked like a charm. As for your crosstable load, that's going to take some time to digest, and since I don't have my registration key yet, I can't look at the file you attached. My company will be providing them to everyone in my department within the month, so I'll revisit this as soon as I'm able. I'm very interested in understanding the capabilities of this program better.

Again, thank you.

[johnw]

I think there's a way to set up my file so you can load it even with personal edition, but I don't know how it's done. In the mean time, I can try to explain a bit what's going on. First, here's what the resulting table looks like:

Client # Contract Division
3995 DEF 1
3995 DEF 2
3995 DEF 3
4278 DEF 1
4278 DEF 2
4637 ABC 1
4637 ABC 3
4728 XYZ 1
4728 XYZ 2
7072 ABC 1
7072 ABC 3
8496 ABC 3
9278 XYZ 1
9278 XYZ 2
9278 XYZ 3

So OK, let's work our way through the script in the basic order it occurs (which isn't strictly the order in which it is written). First, you can see the INLINE [] data. This is supposed to represent your original data that you're loading from Excel or whatever your original source is. That's where things start. As I read the data in, I rename the division fields, replacing them with simply 1, 2 and 3. So you have a table with different column names, but otherwise exactly the same as before. Then I do the crosstable. A typical crosstable load might be done on something like this:

Revenue:
Customer January February March ...
A 1000 2000 1500
B 3000 2500 3000

Notice that there are three parameters for the crosstable(). I'll start with the third parameter. This tells it to just read in the first two columns as is. You want to keep the Client # and Contract as is, so we use 2. In the example table above, we'd use 1, or leave it blank (the default is 1). The FIRST parameter is the field name we want to use to store, in the above case, the values 'January','February' and so on. So we'd use "Month" there. For your data, it's where we store the values 1, 2 and 3. Those are divisions, so we use "Division" there. The second parameter is what we want to call the values. In the above table, that's "Revenue", as each of the numbers represents revenue. For your data, the actual value isn't important. The only thing that's important is that we mark that row as being one we want, or one we don't want. So I named that field "Keep if X". This field will have a value of 'X' for rows we want to keep, and I believe null() for rows we don't want to keep. QlikView doesn't know that we want to exclude rows yet, though, so it will load EVERY combination as part of the crosstable load. Not a problem since the number of combinations is pretty small. Once it has done that, it moves on to our inner join. This is where we tell it that we only want to keep the rows that have an 'X' in the "Keep if X" field. It dutifully tosses out everything else, which are the rows for contracts that do not apply to the specified divisions. Finally, we drop the "Keep if X" field. It has served its purpose and can now be discarded. The result is a table with one ROW per division instead of one COLUMN per division, and we only have rows where the contract applies to that division.