A Historical Odyssey: The Doctors’ Special - Qlik Community

hic · ‎2013-05-21

Already when we were selling QlikView 3, we had received a fairly advanced customer demand from pharmaceutical companies. We solved it. And here’s how we did it. But first some background:

In the pharmaceutical industry, the sales reps are not the ones that sell the products. Instead, they visit physicians and demonstrate one or several products. Days, weeks or months later, the physician prescribe the demonstrated medicine to a patient, and the actual sale takes place when the patient buys the drug at a pharmacy.

The demand on QlikView was to show pharmacy sales data, not only per physician, but also per physician visited once, twice, three times, etc. In other words, the physicians should be grouped by number of visits, and this number should be used as dimension in a QlikView chart. A nested aggregation.

For QlikView 4 we had a solution for this. Well, solution is perhaps not the right word… There was a kludgy, hidden feature with which you could use a count of a field as dimension. Internally it was called the Doctor Controls.

First you had to enable this feature in the list of hidden settings.

Then you could create your chart: Count(Visit) per Physician. After that, you needed to enable the “Display Result Count” in the chart:

The left graph shows graph as-is – without the “Display Result Count” enabled. It shows the distinct count of visits per physician, just as the dimension and expression of the chart are defined.

But, by turning on the “Display Result Count”, the chart transformed into the right graph: The displayed dimension was now the equivalent to an Aggr(Count(Visit),Physician) and the displayed expression was Count(Physician).

We solved a customer’s problem at the time, but this was not a good, long term solution. And it was nothing we wanted to support. Instead we wanted a general solution for nested aggregations. Six years later – after much thinking – Håkan (the Inventor) came up with the Aggr() function for QlikView 7. It is a general function for nested aggregations that made the Doctors’ Special redundant.

But like a relic from the past, the Doctor Controls setting can still today be found in the QlikView 11 list of hidden settings. It doesn’t affect anything - I hope.

HIC