7 Replies Latest reply: Jul 23, 2013 2:04 AM by Marcel Olmo RSS

    Set Analysis Challenge

    Marcel Olmo

      Hello Guys,

       

      I have a table , with Country as a Dimension, and two metrics : Value2 and Value3.

       

      My table looks like this :

       

      Table.png

       

      I'd like to put the Value3 for GBR in all the other Countries. Is there any way to do it by set Analysis?

       

      Many thanks in advance.

       

      Regards, Marcel.

        • Re: Set Analysis Challenge

          Hi Marcel

           

          If I have understood you correctly I think you want to place the value of 5093.211 in all the rows in Value 3 e.g.

           

          CountryValue2Value3Desired Value 3
          GBR478.6585093.2115093.211
          DEU5921.8556805093.211
          ESP4227.89685093.211
          FRA2059.3421195093.211

           

          One way to do this is to use the field value and field index functions.

           

          FieldIndex will detect the row in which the GBR value exists.  Field value will call the value of Value 3 when Country is GBR.

           

          The expression to do this is:

           

          =Sum(FieldValue('Value3',FieldIndex('Country','GBR')))

           

          This will generate a value in each row which you could use to e.g. compare each country's figure with that of GBR.

          e.g.

          =Sum(Value3)-Sum(FieldValue('Value3',FieldIndex('Country','GBR')))

           

          Hope that helps.

           

          Kind regards

           

          Steve

            • Re: Set Analysis Challenge
              Marcel Olmo

              Yes, that's what I want to do. I'm trying to do it like you do. But I can't.

               

              Coud you attach a dummy example?

               

              Many thanks in advance Steven.

                • Re: Set Analysis Challenge
                  Hi Marcel
                  No problem.  I've attached a working example.
                  The example uses an inline load to replicate the first few lines of your data
                  LOAD * INLINE [
                  Country, Value2, Value3
                  GBR,478.658,5093.211
                  DEU,5921.855,680
                  ESP,4227.890,68
                  FRA,2059.342,119
                  ];
                  I then created a pivot chart using the Country as the Dimension.  The three expressions I used to create the Value2 and Value3 and the GBR Value3 values were as follows:
                  Value 2
                  =Sum(Value2)
                  Value3
                  =Sum(Value3)
                  GBR Value3
                  =Sum(FieldValue('Value3',FieldIndex('Country','GBR')))
                  I then added another three expressions to show the comparison between the country represented by the row and the GBR value.

                  GBR-Value3

                  =Sum(FieldValue('Value3',FieldIndex('Country','GBR')))-Sum(Value3)

                  GBR-Value2

                  =Sum(FieldValue('Value3',FieldIndex('Country','GBR')))-Sum(Value2)

                   
                  % of GBR

                  num(

                  Sum(Value2)

                  /Sum(FieldValue('Value3',FieldIndex('Country','GBR')))

                  ,'0.00%')

                  The final pivot table is:
                  CountryValue2Value3GBR Value3GBR-Value3GBR-Value2% of GBR
                  GBR478.6585093.2115093.21104614.5539.40%
                  DEU5921.8556805093.2114413.211-828.644116.27%
                  ESP4227.89685093.2115025.211865.32183.01%
                  FRA2059.3421195093.2114974.2113033.86940.43%
                  The single quotes in the expression for GBR Value3 are important as the functions won't work properly without them.
                  I hope that explains my solution and helps to resolve your question.
                  Kind regards
                  Steve
                    • Re: Set Analysis Challenge
                      Marcel Olmo

                      Wow! That's amazing Steven,

                       

                       

                      Now I see why it doesn't work for me.

                       

                       

                      My problem is that I have a complex model.

                       

                       

                      I have Dimension1, ...., DimensionN and Metric1,...,MetricN.

                       

                      and my sum(Value3) looks like sum( {$< Value1={'1'}, Value2 ={'2'} >} Value3).

                       

                      Maybe the clue is to get an unique key with Dimension1&'-'&Dimension2&'-'&.....&DimensionN as link.

                       

                      and get as well an unique value per each unique dimension.


                • Re: Set Analysis Challenge
                  Vishwaranjan Kumar

                  in Value3 write this expression--

                  if(Country<>'GBR','5.093.211E',Value3)