0 Replies Latest reply: Sep 13, 2011 10:00 AM by Or Shoham RSS

    Multiple selections on same field with one acting as a baseline

    Or Shoham

      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:

      ProductSalesMargin
      A11000100
      B250080
      C3150050
      D41005

       

       

      Selected BaselineProduct: A1

      Selected Products: B2, C3

       

      Chart:

      ProductSalesBaseline SalesRatio
      B2500100050%
      C315001000100%

       

       

       

      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:

      ProductSalesBaseline SalesRatio
      A1-1000-
      B2500--
      C31500--

       

      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,

      Or