Using pivots subtotal (partial facts calculations)

Fri, 12 Aug 2016 18:08:32 GMT

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

Philadelfia Prod2 15 500 33.33

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?

Re: Using pivots subtotal (partial facts calculations)

sunny_talwar — Fri, 12 Aug 2016 18:50:36 GMT

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)

Re: Using pivots subtotal (partial facts calculations)

Fri, 12 Aug 2016 19:04:45 GMT

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)

topic Re: Using pivots subtotal (partial facts calculations) in QlikView

Using pivots subtotal (partial facts calculations)

Re: Using pivots subtotal (partial facts calculations)

Re: Using pivots subtotal (partial facts calculations)