2 Replies Latest reply: Nov 10, 2015 3:16 PM by Suvi Ahlstedt RSS

    Set analysis expression column refers to different dimension value

    Suvi Ahlstedt

      Hi all,

      I have little problem with set analysis expression. I´m trying to achieve a chart with columns which refers to different dimension value than showing on dimension column and I got it partly working but not totally correct.

      Here is example, let´s say that I have 6 stores (A,B,C, A1, B1, C1) and I have to calculate sales of store A but at the same row, I have to show A1 sales also, something like that:

         

      SalesAdditional salesTotal sales
      AStore10025125
      BStore501565
      CStore751085

      This column should contain sales as follows:

      AStore line Additional sales = sales of A1Store

      BStore line Additional sales = sales of B1Store

      CStore line Additional sales = sales of C1Store

       

      I wrote expression

      if(WildMatch(StoreName,'AS*'), Sum({<#Store={'A1'}>} total Sales),

      if(WildMatch(StoreName,'BS*'), Sum({<#Store={'B1'}>} total Sales),

      if(WildMatch(StoreName,'CS*'), Sum({<#Store={'C1'}>} total Sales)))

      And it worked fine until I had to add another dimension (Time cyclic group = Year/Quarter/Month...). After that, each row shows total sales for selected time period, not sales by month (for example). If I remove total from expression, I got 0 as result.

      Is there any way I can accomplish that kind of chart?