Dealing With Partial Data In Total Row

billbois — Fri, 28 Jun 2013 18:43:54 GMT

OK, this could be difficult to explain, so bear with me. I have a pivot table that uses set analysis and/or alternate states in most of its columns. The user can select Insurer A and Insurer B to compare the two's rates for particular codes. The table looks like this:

Code	Ins. A Revenue	Ins. A Cases	Ins. A Rate/Case	Ins. B Revenue	Ins. B Cases	Ins. B Rate/Case	% of Ins. A
100	$ 2,815	1	$ 2,815	$ 5,325	2	$ 2,663	94.58%
101	-	-	-	$ 5,276	2	$ 2,638	-
102	$ 3,240	2	$ 1,620	$ 9,546	6	$ 1,591	98.21%
103	$ 6,233	3	$ 2,078	$ 11,546	7	$ 1,649	79.39%
104	$ 1,842	1	$ 1,842	$ 3,748	3	$ 1,249	67.82%
Total	$ 14,130	7	$ 2,019	$ 35,441	20	$ 1,772	87.79%

The last column should show Ins. B's Rate/Case divided by Ins. A's Rate/Case, as a percentage.

It works great when the data is complete. However, note that Ins. A has no cases for Code 101. This skews the grand total in the "% of Ins. A" column because it includes Ins. B's data for Code 101.

What I need to do is exclude Code 101 when figuring out Ins. B's Rate/Case, but only in the total row for "% of Ins. A." When I do so manually, the total "Ins. B Sum" becomes $30,165, divided by the total "Ins. B Cases" of 18, which gives me a total "Ins. B Rate/Case" of $1,676, for a "% of Ins. A" of 83.02%, not the 87.79% shown. (Note that I don't want to exclude Code 101's data from the other columns in the Total row, only the "% of Ins A" column.)

I never know in advance what two insurers the user will select, so I don't know if Ins. A or Ins. B will have no cases for a particular code. I have to allow for either possiblity.

The drop-down list from which the user selects Ins. A is set to the alternate state "InsA." There is no alternate state for Ins B. To further complicate matters, Ins. A can include any Classes the user has selected, while Ins. B can include only Classes 1 and 2.

The expression I use for the "% of Ins A." column is this:

(SUM({InsA<@ID=P({$<@Class={$(vClass)},>}@ID)>}Revenue)

/SUM({InsA<@ID=P({$<@Class={$(vClass)}=>}@ID)>}Cases))

(SUM({$<@ID=P({$<@Class={1,2}=>}@ID)>}Revenue)

/SUM({$<@ID=P({$<@Class={1,2}>}@ID)>}Cases))

It works great on every row except the total. Any suggestions?

Re: Dealing With Partial Data In Total Row

billbois — Mon, 01 Jul 2013 16:08:15 GMT

Monday morning bump. Thanks to everyone who's looked at this!

topic Dealing With Partial Data In Total Row in QlikView

Dealing With Partial Data In Total Row

Re: Dealing With Partial Data In Total Row