# QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

## Using pivots subtotal (partial facts calculations)

Hello,

Using pivot table as one of the ways to provide drill down.

Issue is that one of the base metrics that is used in consequent calculation is loaded as partial fact (utilized link table).

Here is an example.

Region    Market             Products       Membership    Sales   Sales PMPM*

East        Virginia            Prod1            4                      20            5.00

Prod2            7                      80            11.43

Market Total:                        10                   100          10.00

Prod3            85                   1000          11.76

Market Total:                       100                 1500          15.00

Region Total                                       110                 1600          14.55

So membership is loaded as partial fact, sales data is normal.

Sales PMPM (per member per month) highlighted in red is not quite what I need here. The need is to use Market Subtotal as a base for Product PMPM calculation.

Can it be done and how?

1 Solution

Accepted Solutions
MVP

## Re: Using pivots subtotal (partial facts calculations)

So you need to get 100/4 = 25 for Prod1 and 100/7 = xxx fpr prod2?

Try this:

Sum(TOTAL <Region, Market>Sales)/Sum(Membership)

2 Replies
MVP

## Re: Using pivots subtotal (partial facts calculations)

So you need to get 100/4 = 25 for Prod1 and 100/7 = xxx fpr prod2?

Try this:

Sum(TOTAL <Region, Market>Sales)/Sum(Membership)

Not applicable

## Re: Using pivots subtotal (partial facts calculations)

Hi Sunny,

Thank you for the solution! It worked.

Actually needed 20/10 = 2 for Prod1 and 80/10 = 8 for Prod2. So 2 + 8 = 10.

SUM(Sales)/SUM(TOTAL <Region,Market>Membership)