How to write formula paid media category?

Question & Answer
,
1

Hi All,

i would like to create calculation filed to grouping paid media in column C and need to write formula.

i would like to use grouping by campaign name.
Example

1 FB contain text = “facebook” or “fb”
2 GDN contain text = “GDN”
3 SEM contaion text = “SEM_xxx” or ‘sem_xxx’

image

ps. now i use ifelse formula

ifelse(in({campaign_name},["FB_BigPA23", "FB_BigTAdomes23"]), "Facebook",
in({campaign_name},["GDN_BigTAdomes23", "GDN_BigTAinter23"]), "GDN",
in({campaign_name},["KOL2"]), "KOL",
in({campaign_name},["brand_sem_SCB_pa adult_Conversion_mix"]), "SEM"

, "Other")

Thank for help
Siriphon

2

Hi @Siriphon You should be able to use contains - Amazon QuickSight function to achieve what you have described. Let us know if this works? Thanks.

1 Like
3

hi
i try to use contain but got some error.

thanks

4

@Siriphon could you post the formula that you used with contains and the corresponding error screenshot please? Thanks!

5

Hi
this is error.

ifelse({campaign_name} = contains({campaign_name}, "fb"), "FB", 
"other")

image
image1461×923 58.2 KB

6

@Siriphon The error is in the formula, just remove everything before ‘=’

Here is a very rudimentary example:

ifelse(contains(Status,“ai”),“Fail”,“Other”)

image

1 Like
7

it work many many thanks !!!

image
image326×559 16.5 KB

ifelse(contains({campaign_name}, "FB"), "FB", 
contains({campaign_name}, "SEM"), "SEM",
contains({campaign_name}, "GDN"), "GDN",
contains({campaign_name}, "KOL"), "KOL",
contains({campaign_name}, "CI3E"), "FB",

        "Other")
8

can i use function in() after contain()

ex.

ifelse(contains(in({campaign_name}[“FB_BigPA23”, “FB_BigTAdomes23”]), “FB”,

i found an error.

image
image880×79 4.21 KB

9

@Siriphon may I ask what do you want to achieve by using ‘in’?

10

i would like to grouping

CI3E , FB_BigPA23 , FB_BigTAdomes23,TAAll15NY2023,TAInbound55 as a FB

use contain and in formula

many thanks

11

@Siriphon I am not sure I understood, but I would suggest you use OR operator along with contains if you want to group multiple values into FB for example…

1 Like