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

# Dynamic Calculated metric

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

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

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

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

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