Qlik Community

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:

CodeIns. A RevenueIns. A CasesIns. A Rate/CaseIns. B RevenueIns. B CasesIns. 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:






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

1 Reply
New Contributor III

Re: Dealing With Partial Data In Total Row

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

Community Browser