Hi there. Is there any way to count the occurrence of selected value (within a filter) over multiple columns? For example, let’s assume a database of clients that are ordering cakes with different flavors. The flavors data is held in 6 columns and I need to be able to create a visualisation that allows the user to filter on specific unique flavors and shows how many times that flavor has been selected. I have tried to find a working solution using parameters, calculated fields or some sort of unpivot, but I can’t seem to find the best approach. If it helps I need to achieve something similar to this:
Hello @Alina_Benchea - Welcome to the Community! Thanks for posting your query. Based on the problem statement that you have shared, it seems more of a data representational problem which need to be resolved during the data preparation stage. Ideally you should only have 2 columns - Client ID and Flavor. Hence you need to use a SQL query like below to change the format of the dataset before you can consume it in QuickSight :
select client_id, flavour_1 as flavour
from table
union all
select client_id, flavour_2 as flavour
from table
union all
select client_id, flavour_3 as flavour
from table
union all
select client_id, flavour_4 as flavour
from table
union all
select client_id, flavour_5 as flavour
from table
union all
select client_id, flavour_6 as flavour
from table;
Once you are done with this dataset, then you can create your visualizations easily. Hope this helps!
Did my suggestion help you in resolving your query? If yes, would request you to mark the post as “Solution”. This will help the community to find guidance and answers to similar question. Thank you!
2 Likes
This was really helpful! Thank you for this
2 Likes