-Contain/IN Function

I have data in 2 rows and 2 columns like -

data count
india germany england 7
india england france 4

If I create a new column - CountryList Like:-
Ifelse(contains(data,‘india’,‘India’,
contains(data,‘england’,‘England’,
‘others’)

For this new columns created Count for India should be 11 and england should be 11 as well.
But its only considering one contains statement and ignoring other . hence If count for India is 11 , then count for england is not coming as 11.

Thank you.
Data

Hi @Vivek54 - Can you please create the calculated field with below logic.

ifelse(contains({data},'india'),sumOver({count},['india'],PRE_FILTER),contains({data},'england'),sumOver({count},['england'],PRE_FILTER),0)

See the sample output

image

Regards - Sanjeeb

1 Like

Hi sanjeeb, This calculation is giving half the solution…

But how can I have a filter dropdown with list of all the countries. I tried with a parameter, but its not filtering your calculation.

Hi @Vivek54 - Please provide the detail on the requirement so that it will help in guiding you the right solution.

Regards - Sanjeeb

Hi Sanjeeb,
We need to provide users with a country name drop down, with all the countries listed in the data column.

Now
If User Selects India Count should be 11.
If User selects England Count Should be 11.
If User selects Germany Count should be 7.
if user selects France count should be 4.

Hope this is clear.

1 Like

Ok @Vivek54

Hi @Biswajit_1993 - Any Quick tips on this ?

Regards - Sanjeeb

Hi @Vivek54

Can you try making your control with the parameter. You will need to hard code the values in as your dataset doesn’t seem to have those values as a unique column.

Then it should look like this.

sumOver({count},[${parameter}],PRE_FILTER)
1 Like

Hi @Max

Can we use parameter as partitionby field? As I am getting error when using the calculation you suggested.

Thankyou

Hi @Vivek54

Yes you can. What’s the error you are getting?

Hi @Vivek54 , Can you used one parameter as suggested by the @Max and validate it.
If you will have any issues please revert back.

Bit tricky concept it is really help us to gain knowledge and improving your self.

Thanks & Regards
Biswajit Dash

1 Like

Hi Max.

I created country parameter and wrote this calc:
sumOver({count},{CountryParameter}],PRE_FILTER) .

Error:-

Expression sumOver({Count},[${CountryParameter}],PRE_FILTER) for function sumOver has incorrect argument type sumOver(Number, String List, ). Function syntax expects Numeric, List, Calculation Level Numeric, List ‘sumOver(Number)’.

I have validated the datatype for measure is numeric only.

If I Remove From calc, i.e ( sumOver({Count},${CountryParameter},PRE_FILTER)
This calculation is not showing any error and the output is 11 for both rows. when enabling country filter to using parameter, everything goes blank.

Hi All - @Vivek54 , @Sanjeeb2022 , @Max , @Biswajit_1993

Let’s not over complicate this. What @Vivek54 wants to do can be accomplished with a Custom filter having Contains condition. See screen shot below.
The drop down value set will need to be provided as a static set.
I’m marking this as solution to this question for now. Let us know if there are further questions.

Regards,
Arun Santhosh

1 Like

Thanks @ArunSanthosh . Yes, this is a very efficient solution.

Regards - Sanjeeb

Hi Arun.

Yes, this solution will work. But I wanted to have a multi select parameters. and multi select parameter, won’t work with contains.

Hi @Vivek54 ,

Yes. It can’t be used with multi select parameters. I suggested that approach since the explanation provided seemed to indicate need for single select and it wasn’t mentioned that you needed to multi-select.

We can use a slightly different approach to address multi select use case. This is recommended only if the max number of countries per record is a known and low number. (In your example, you have 3 countries per record. You should be able to extend below solution to say 10 countries per record.)

You should create calculated fields that extract each of the countries from the list.
Country1
split({Country list},' ',1)
Country2
split({Country list},' ',2)
Likewise, create Country3 etc.

Create a multi select parameter and a control with static values.

Create a filter group that compares each of the calculated fields with the multi select parameter. These filters are grouped together with OR condition. (See below)

Now, setting the control values should give you the desired results (see below).

If you have large number of countries that can be present in each record, you should explore reshaping your data ahead of bringing it to QuickSight.

Regards,
Arun Santhosh

4 Likes

Thankyou @ArunSanthosh . Really Appreciate your time and effort.

1 Like

Most welcome @Vivek54

Regards,
Arun Santhosh

1 Like