How do I group certain values of a field to one new value, within set-analysis, keeping the rest untouched?

[#1]

Hi all,

I want to build an object based around "Marketing Sources" for leads that my company generates.

There are many possible values to this field, and I'd like to group some of them together when I build straight tables, charts, etc. Marketing Source is one of the dimension.

I'm using set analysis in my expression for regular purposes, for example:

Count ( { <[Date Diff]={">$(=Max({< [Date Diff]={'<=0'}>} [Date Diff])-11)"}, Status={'Lead CreatedDate'}, [Marketing Source]-={'Excluded_Source1''},[Marketing Medium]-={'Excluded_Medium1'}>}[Lead Id])

I assume I need to use calculated dimensions but I couldn't get it to work properly (i.e. show anything). Also, not sure how I can define in a calculated dimension to only group certain fields and keep the rest as is.

For example, I'd like to group:

'PPC Google', 'PPC Bing', 'PPC Other' to just 'PPC'

'Google','Bing' to just 'Search'

Would appreciate your help with this one!

Thanks!

[tresesco · Accepted solution]

May be like(calculated dimension):

=If( WildMatch([Marketing Source], 'PPC*' , 'PPC' , If(Match([Marketing Source], 'Google' , 'Bing') , 'Search' ,[Marketing Source]))

Note: The highlighted part keeps your values untouched if conditions don't match.

[#3]

exactly what I want say. Just replace your old dimension with the calculated one.

Or may be you can hard code an additional column in your load script, like 'Type', and decide PPC or Search there. Just another option.

[#4]

Seems to work.

I was very close then. Not sure what went wrong.

Thanks both!