# QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

New Contributor III

## Dealing With Partial Data In Total Row

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?

Tags (5)