Calculated field text grouping

Question & Answer
, ,
1

Hello, I am working on a dashboard and I would like to know if someone might have had the problem that I have.
I have this calculated field that groups what you want by number of touchpoints that you have in a path:

ifelse(
contains(path, ‘[4’, CASE_INSENSITIVE), ‘4’,
contains(path, ‘[3’, CASE_INSENSITIVE), ‘3’,
contains(path, ‘[2’, CASE_INSENSITIVE), ‘2’,
contains(path, ‘[1’, CASE_INSENSITIVE), ‘1’,
‘Others’)

However, I would like to group by words too, for example group by ‘SBV’ and see how many purchases were made with a path containing an SBV ad.
I did the same query but like this:

ifelse(
contains(path, ‘SBV’, CASE_INSENSITIVE), ‘SBV’,
contains(path, ‘SP’, CASE_INSENSITIVE), ‘SP’,
contains(path, ‘SD’, CASE_INSENSITIVE), ‘SD’,
‘Others’)

It works but there is a problem, for example here the first one is SBV, so when I insert a visual the purchases that have SBV will all be there, but then SD won’t count those purchases even though sometimes the paths have SBV and SD.

I think there might be something like the SQL LIKE %SBV% to take into account no matter what is before or after the word

Hope I made myself clear, thank you

1 Like
2

Hello @d.torrez, welcome to the QuickSight Community!

So, since you are using an ifelse statement, if the first statement passes on the row it is checking, it will not also check the second statement. No matter what, it will only return the first value that it checks for. If a single field can contain multiple of the values you want to count, you may want to consider checking for each case in a different calculated field.

You can do something like this:
SBV = ifelse(contains(path, 'SBV', CASE_INSENSITIVE), 1, 0)
SP = ifelse(contains(path, ‘SP’, CASE_INSENSITIVE), 1, 0)
SD = ifelse(contains(path, ‘SD’, CASE_INSENSITIVE), 1, 0)

Others = ifelse(
contains(path, ‘[4’, CASE_INSENSITIVE), 0,
contains(path, ‘[3’, CASE_INSENSITIVE), 0,
contains(path, ‘[2’, CASE_INSENSITIVE), 0,
contains(path, ‘[1’, CASE_INSENSITIVE), 0,
1)

Once you have these functions, then you can check the totals for each group. I’ll mark this as a solution, but if you have any follow-up questions, please let me know!

3

Thank you very much for your answer !
I still have a question I am sorry, the calculated field with the numbers was an example.

I would just like to use SD, SBV and SP and see how many purchases there are for each one of these even if sometimes they are in the same path.
What do I need to write in the calculated field? Should I write a fonction for each format (SP, SD and SBV)? Or should it be only one generic fonction ?

Thank you.

4

Hello @d.torrez, you would still need to do this in 3 different calculated fields. The reason for that is, if you are checking for all 3 items in a single ifelse statement, it will only return a value for 1 of them even if more than 1 is true. Once it passes the first contains if statement, the others will be ignored.

If you run the functions separately though, you can get the total number for each.