One of the most common questions I have received when speaking with Pharma clients is how to avoid double counting prescription sales over a many-to-many sales organization. In this scenario, each Sales Territory has many Prescribers and each Prescriber can have many Sales Territories. More specifically, in a chart (table) object, how do you show the correct detail row total as well as the correct overall total when the sum of the detail rows does not equal the overall total?

[For the purposes of brevity and clarity, this article will cover mirror or shared alignments only. Apportioned alignments are also possible but a more complex model and discussion is required.]

In Qlik, this happens quite naturally, assuming your data model is structured properly between Fact and Dimension. The most important consideration is the Fact data must be unaligned to the sales force. The sales relationships must be maintained separately from the Fact data in the Qlik model. This will insure accurate summing of the Fact rows, regardless of the relationship complexity or the number of associated dimensions.

Keep in mind that when the Qlik engine calculates a chart object, the chart detail rows are constrained by the associated dimension values on that row (unless the TOTAL qualifier is specified in the expression of course), but Totals are calculated separately over the full amount of data available to the expression, regardless of the dimensions. This is known as Dimensionality in Qlik. It applies to all chart objects, but can be mainly seen in Straight and Pivot Table charts. Since it is an aspect of the QLIX engine, it applies to both QlikView and Qlik Sense.

A simple example may help. Consider a sales force with 2 Territories and a universe of 3 prescribers as follows. Notice that 1 Prescriber (John) is shared between both Territories, but his TRX is only considered once in the Chart TOTAL. Also notice that the Chart TOTAL and the Fact TOTAL are always equal for the same selection state.

So, even though John’s 10 TRX are aligned to both Territory A and B, and they are included in each detail row of the Chart, they are only counted once in the Chart TOTAL. By doing this, Qlik will always provide the correct detail result as well as the correct TOTAL result for all charts, regardless of dimension.

Phil Bishop, Managing Partner