4 Replies Latest reply on Aug 24, 2017 9:27 AM by Marc odonnell

    Dynamic Calculated metric

    Marc odonnell

      Hello,

      I am familiar with calculated metric where I can enter the formula

       

      IF [Dimension 1] = 'aBc' THEN sum ([metric1]) END

       

      However if I want to recreate this across all the values of Dimension 1 is there a way to do this dynamically without creating one calculated metric at a time.

       

      My end goal is to do some comparisons between Dimension 1 + Metric 1 and Dimension 2 + Metric 1. Dimension one and 2 have the same set of values. I'm hoping I dont have to go back and manipulate the source data even though this is my only workable solution at present.

       

      Thanks,

      Marc

        • 1. Re: Dynamic Calculated metric
          Ben Neville

          This might be easier if we had some sample data to help give a better idea of how this applies to your specific use case, but there's no reason you can't add multiple arguments to a single calculation.

           

          SUM(IF [Dimension 1] = 'abc' THEN [metric 1]

          ELSEIF [Dimension 1] = 'def' THEN [metric 2]

          ELSEIF [Dimension 1] = 'ghi' THEN [metric 3]

          ELSEIF [Dimension 1] = 'jkl' THEN [metric 1]

          ELSEIF [Dimension 1] = 'mno' THEN [metric 2]

          ELSEIF [Dimension 1] = 'pqr' THEN [metric 3]

          END)

           

          You could also shorten this to:

           

          SUM(IF [Dimension 1] = 'abc' OR [Dimension 1] = 'jkl' THEN [metric 1]ELSEIF [Dimension 1] = 'def' OR [Dimension 1] = 'mno' THEN [metric 2]

          ELSEIF [Dimension 1] = 'ghi' OR [Dimension 1] = 'pqr' THEN [metric 3]

          END)

           

          Or even further (at least by characters) to:

           

          CASE [Dimension 1]

          WHEN 'abc' THEN [metric 1]

          WHEN 'def' THEN [metric 2]

          WHEN 'ghi' THEN [metric 3]

          WHEN 'jkl' THEN [metric 1]

          WHEN 'mno' THEN [metric 2]

          WHEN 'pqr' THEN [metric 3]

          END)

           

          Hope that helps. There are several training videos on calculation syntax which may help you further.

          • 2. Re: Dynamic Calculated metric
            Marc odonnell

            Hey Ben,

             

            not sure if I'm approaching or explaining this correctly.

             

            What I have is two dimension sets. Dim 1 Dim 2

            I have only 1 Metric which is sales qty.

             

            Dim 1 & dim 2 both have the same list of values as they are marketing channels. However Dim 1 is first touch channel and dim 2 is Last touch channel.

            DIM 1                    DIM 2

             

            Direct                  Direct                   

            Organic               Organic    

            SEM                    SEM

            Social                  sOCIAL

            Email                   Email

            Content               Content

            Other                   Other

            Referrals             Referrals

             

            What I want to do is compare the sum of Direct first touch vs Direct Last touch to get a % difference.

             

            I think I can do it if I manually create the 8 calc metrics for each 1st and last touch which I can compare against each other. It doesn't seem the most efficient way though and when I go to sub-channel level isn't workable.

             

            Thanks,

            Marc

            • 3. Re: Dynamic Calculated metric
              Ben Neville

              Ah, so if I understand you correctly, you might have 3 lines that appear something like the following:

               

              SEM         Social

              Email       Content

              Organic    Other

               

              The key here being that the values aren't matching across dimensions (even though there are only ~8 different options that are repeated in each column).

               

              Is this correct?

              • 4. Re: Dynamic Calculated metric
                Marc odonnell

                Yes thats correct

                I will have many combinations, probably the full 64 rows when all considered. However what I am interested in comparing is the aggregated

                1st touch sales qty Direct Vs last touch sales qty Direct. For each of the 8 values.

                 

                I'll get into the various combinations at some stage in the future