This is an interesting ask. I'm not aware of a way to do this with variables. I would probably recommend adding this data to the dataset via an ETL and then you could use a combination of filter cards (one for region and one for Country) to accomplish this on a dashboard. However, maybe you could reach out to @chucki to discuss your use case. He may have something cooking in the beta program that we don't know about :)
Are you wanting to be able to filter based on a list of items? Try this:
- CASE
- --Checks from list using “:” to determine start and end of item name
- WHEN CONCAT(':', REPLACE(UPPER(REPLACE(`Var.ItemList`, ',', ' ')), ' ' , ':'),':') LIKE CONCAT('%:', UPPER(`ItemName`), ':%') THEN 1
- --Checks for direct match
- WHEN UPPER(`Var.ItemList`) = UPPER(`ItemName`) THEN 1
- --If nothing is entered, display everything
- WHEN UPPER(`Var.ItemList`) = '' THEN 1
- ELSE 0
- END
What this does is replace all blank spaces and commas with colons to seperate each item the user adds to the variable. It then checks if the column in the dataset you're referencing matches an item in the itemlist variable. Additional checks are added if the variable is an exact match to a column (if only 1 item was entered) or if it's left blank (nothing is filtered in this case).
For example, variable has input of "SPAIN, France, Germany", this is convered to ":SPAIN::FRANCE::GERMANY:" and then checks for a match with the "ItemName" column.
If you're wanting the variable to be a static list, you can set the variable to be defaulted to whatever you like. From that point, any time your reference that variable, it will be your static list.
One thing to note, with your example, "South Korea" contains a space which will cause this solution to search for "SOUTH" and "KOREA" seperately. I haven't tested this but you can likely swap the "UPPER(`ItemName`)" section on line 3 with
- REPLACE(UPPER(`ItemName`), ' ', ':')
In theory, this will convert "South Korea" to "SOUTH:KOREA" and create a valid match when compared to the variable ":JAPAN::CHINA::SOUTH:KOREA:"
@ST_-Superman-_ for more context, the reason I want to use variables because I have a Beast Mode that looks like the below:
CASE
WHEN Region = 'EMO' THEN
CASE WHEN Contact Country IN ('Belgium', 'Finland', 'France','Germany',
'India','Ireland','Israel','Israel',
'Italy','Netherlands','Spain','Sweden',
'United Kingdom') THEN Contact Country
ELSE 'Other'
END
ELSE 'Other'
END
and I want to replace the countries that is in the IN bracket with a variable, where a list of countries can be added.
We've achieved this and other dynamic solutions with variables through Workflows and I've also added the suggestion to the Ideas Exchange if you want to upvote it here:
Lindsay Gillespie Analytics Practice Manager | Graphable | lindsay@graphable.ai
💡 Need expert help? Let’s connect! https://www.linkedin.com/in/lindsay-gillespie/
Say thanks by clicking 👍 on helpful posts
