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

    Dynamic Calculated metric

    Marc odonnell


      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.




        • 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]



          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]



          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]



          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.




            • 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