Multiple selections on same field with one acting as a baseline

Or — Tue, 13 Sep 2011 14:00:28 GMT

Dear all,

I am looking for a way to allow multiple selections on the same "field". One selection is forced as a "Always one selected" and provides a baseline for comparison, while the second selection can be multiple choices. What I would like to achieve is a chart showing the selected second-selection, and the ratio between the baseline selection and the second selection.

For example, the following data:

Product	Sales	Margin
A1	1000	100
B2	500	80
C3	1500	50
D4	100	5

Selected BaselineProduct: A1

Selected Products: B2, C3

Chart:

Product	Sales	Baseline Sales	Ratio
B2	500	1000	50%
C3	1500	1000	100%

What I've done so far, with limited success, was make a second copy of the Product field (Load distinct Product as BaselineProduct resident Sales), set it as "Always one selected", and then assorted versions of set analysis in the chart. My best effort to get the baseline value ended up being:

=sum({$<Product= {$(=only(BaselineProduct))} >} Sales)

Which does indeed get the baseline sales correctly when Product is not a dimension. Unfortunately, when using Product as a dimension, this results in the following chart:

Product	Sales	Baseline Sales	Ratio
A1	-	1000	-
B2	500	-	-
C3	1500	-	-

which is not what I had in mind.

I'd appreciate any suggestions as to how to get this done (perhaps using variables? Different set analysis? something else?).

TIA,

topic Multiple selections on same field with one acting as a baseline in QlikView

Multiple selections on same field with one acting as a baseline