3 Replies Latest reply on Jun 29, 2018 10:14 PM by swaroop.gantela

    Comparing Inlist values of two dimensions

    dinesh punnam

      Hello Community,

       

      I have two dimension ( Sales office and Region) Want to compare the values in the both dimension (please find attached excel) and show respective meaures - if there is nothing matching (ex.unites states is not in region so we want to see full sum of all values)

       

       

      Thanks

      Dinesh

        • 1. Re: Comparing Inlist values of two dimensions
          swaroop.gantela

          Dinesh,

           

          I'm not sure if this got there, but maybe it can give an idea.

           

          This next conditional is specific to the sample set, will likely

          need to be modified for specific cases.

          Mark matches using:

          IF LEFT([Sales Office],2)=LEFT([Region],2)

          OR (LEFT([Sales Office],2)="Pa" AND LEFT([Region],2)="Ce")

          THEN 1 ELSE 0 END

           

          If no matches, then sum all Regions, otherwise

          fix the match measure value to the Sales Office:

          IF { FIXED [Sales Office]:SUM([Has Match])}=0

          THEN { FIXED [Sales Office]:SUM([Measure])}

          ELSE { FIXED [Sales Office]:SUM(

          IF [Has Match]>0 THEN [Measure] ELSE 0 END)}

          END

           

          Please see workbook attached in Forum Thread.

          • 2. Re: Comparing Inlist values of two dimensions
            dinesh punnam

            Swaroop,

             

            Thanks for your response

            I am able to get Has match calculation but cannot achieve below as the measure below is already coming out of LOD (please see CPM  in attached twbx,

             

            IF { FIXED [Sales Office]:SUM([Has Match])}=0

            THEN { FIXED [Sales Office]:SUM([Measure])}

            ELSE { FIXED [Sales Office]:SUM(

            IF [Has Match]>0 THEN [Measure] ELSE 0 END)}

            END

             

            Ia m trying to substitute [measure] with CPM but as it was already in LOD - I see aggregation over aggregation error

             

             

            Thanks,

            Dinesh

            • 3. Re: Comparing Inlist values of two dimensions
              swaroop.gantela

              Dinesh,

               

              That does complicate things.

              I was able to get numbers that looked right,

              but it took quite a few steps.

              There is probably an easier way.

               

              This is the new version, combining LODs with Table Calcs.

              Probably call all be done in LOD, but was just trying to get

              something working first:

              IF SUM({ FIXED [Sales Office]:SUM([Has Match])})=0

              THEN

                  IF FIRST()=0 THEN

                  WINDOW_SUM(SUM( { INCLUDE  [Sales Office]:[CPM]}))

                  END

              ELSE IF SUM([Has Match])>0 THEN SUM({ INCLUDE  [Sales Office]:[CPM]}) ELSE 0 END

              END

               

              This calculation doesn't work well with Grand Totals,

              and so I put them on a separate sheet.

              There may be a better way to get the Grand Total:

              Why Your Grand Total or Subtotal Isn't Working as Expected

               

              Please see workbook attached in Forum Thread.

               

              Will continue exploring for simpler way.