HI,
I have 5 columns with Week numbers as content. Would like to create a field/filter that takes input from all 5 columns. Say if I want to view the data for week 12, the rows having week 12 in all 5 columns should be displayed. How can I achieve this ?
1 Like
Hello @suraruna, welcome to the QuickSight community!
In order to accomplish this, you will need to utilize a combination of parameters with calculated fields. If you set a parameter linked control to either display the options linked to one of the 5 columns or manually enter each of the week number options available, you can then use that selection to calculate the filter. In order to use it in a calculated field, you will need to set a default value to the parameter.
Then, if your parameter values will match the value displayed in your column, the solution will be easier. You will write an ifelse calculation to check if the parameter value matches the field value. It would look like this:
ifelse(${Week} = {Column 1} AND ${Week} = {Column 2} AND ${Week} = {Column 3} AND ${Week} = {Column 4} AND ${Week} = {Column 5}, {Column 1}, NULL)
You will not need to return all of the column values to ensure the row is returned, you just need to return a single value and then add the other columns accordingly. You will then add a custom filter for this calculated field and make sure exclude nulls is selected in the dropdown. Now, this will get more tedious if the parameter value doesn’t match exactly. In that case, if the week number is in the column value and the parameter, than you can use the substring aggregation to pull the Week number out of both and see if they match so it will work dynamically.
That should get you the expected result. I’ll mark this as the solution, but let me know if you have any questions!