6 Replies Latest reply: Jun 7, 2016 9:38 AM by annick whitfield RSS

    KPI : Link and compare 2 measures with different dimensions

    annick whitfield

      Hello,

       

      I would like to compare 2 measures "Sales Amount"  and "Target Sales Amount"

       

      Here's my load script:

      Sale:
      Load * inline
      [
      SaleID, SaleAmount, SaleYear, SaleMonth, SaleCity, SaleDay
      101,     100,                2016,       1,                PAR,       10
      102,      10,                 2016,       1,                LON,       11
      103,      150,               2016,      3,                 HEL,        9
      104,      210,               2016,      4,                 FRA,       2
      105,      40,                 2016,      4,                 PAR,     13
      106,      345,               2016,      8,                 LON,     21
      107,      35,                 2016,      8,                 FRA,     25
      ];


      Target:
      Load * inline
      [
      TargetYear, TargetMonth, TargetCity, TargetAmount
      2016,  1,    PAR,  20
      2016,  1,    LON,  30
      2016,  2,    PAR,  10
      2016,  2,    LON,  20
      2016,  5,    HEL,  15
      2016,  6,    PAR,  10
      2016,  6,    LON,  20
      2016,  7,    PAR,  15
      2016,  8,    PAR,  20
      2016,  9,    HEL,  15
      2016,  10,    HEL,  5
      2016,  11,    HEL,  0
      2016,  12,    PAR,  15
      2016,  12,    LON,  25

       

      ];

       

       

      In the UI I have the following KPI:

      Sale Amount = Sum(SaleAmount)

       

       

      I would like to add a KPI which is the sum(TargetAmount) for TargetMonth = SaleMonth  and TargetYear = SaleYear.(i.e. SaleMonth and TargetYear as selected above).

       

      Is there a way to achieve this in the UI without changing the load script ? if not possible, what would be the best way, knowing that the filters might evolve in the future i.e. : SaleCity, SaleDay etc...

       

      Many thanks.

      Annick