Have value based on selected value for all rows

Question & Answer
1

Hi,

I have little bit problem with calculations so below is explanation for the issue
Scenario 1
image

Scenario 2
image

I would like to have the expected value based on selection.
I did tried to using this calculation but it’s only giving me value of all total based on activity selected and there will be no effect if other filter has been selected.

sumOver(ifelse(${pActivity} = {activity (short name)} , value, 0),,PRE_FILTER)

Please help! Any help will be very appreciated!
Thanks

2

Hello @consultantcon , I have a few quick questions so I can try to guide you in the right direction. What is the value field looking to accomplish? Is it trying to display the number of rows that match with the parameter filter?

If that is the case, is the table being filtered down as well or are you still trying to display all of the rows and the filter is only being used to handle the value being displayed?

1 Like
3

Hi Dylan,

Thank you for your reply, so actually I want to have the “Expected” column that will always change based on selection. If you see in scenario 2, it’s show 5 because of 2+3 (based on sports, all age, male gender, and all year)
the expected fill following the parameter and all filter selected.
While the behaviour of parameter is only single selection, and other filter user can select multiple values.
I hope the answer is clarify.

1 Like
4

Hi @consultantcon , there could be a route where you create a calculated field with a nested ifelse that might be able to check for all of the filter scenarios that you do not want to include in the calculation for the “Expected” column value. That would get messy though and I can’t guarantee it will work how you want.

I think the best way to display the value you are wanting would be to remove the column created by the calculated field from the table and display it as a separate KPI. Since it is a new visual you can exclude it from the filters that you do not want to change the desired value. This will be the easiest route to ensure the “Expected” value is returned.

5

Hi Dylan,

Thanks for your reply.
I did try to use if like this
sumOver(ifelse(${pActivity} = {activity (short name)} AND ${pGender} = {gender}, value, 0),,PRE_FILTER)
but it gives me error because the gender is multiple value (as my understanding)
Maybe can help me what kind of nested if I can try for this issue?

6

Hello @consultantcon ,

I want to make sure I have all of the details correct on this.

  • Do you have parameter based controls connected to all 4 values you are filtering for? As in the user has the ability to control what is selected for each of the values and when selected it alters the filter value and sets the value in a parameter.

  • When the filters are selected, do they all control what is displayed on the table? I know your above examples are spreadsheet examples, but I just want to make sure they handle the visual display and not just calculated fields. i.e. If female is selected for Gender, only rows that include female are displayed in the table.

I will try to build something out that could handle this scenario regarding the details you provide.

I do think the better way to handle the value would be outside of the table if it is the only column that you want to exclude from the filters. I can’t promise that it will provide the correct values even if we make a more detailed nested ifelse, but I will see what I can build out.

7

Hi @DylanM

for point one, if it’s needed using paramter, it’s also ok to use parameter
for point 2, yes if female selected then only rows that include female will be displayed

Thanks

8

Hello @consultantcon I think I have the best solution to ensure your Expected value always shows correctly. You will set up the pActivity parameter as you have it. Give it a default value of “All”. Then do not attach it to a filter on the table. Just build out a control that allows the user to select the activity.

Then set up parameters for the values you want to filter by and add controls for those as well so Gender, Age, etc. can be selected by the user. You can then add filters for those fields to control the table based on the parameter value selected.

Now use the calculated field “sumOver(ifelse(${pActivity} = Activity , Value, 0), [Activity],PRE_FILTER)”, so that the values will be added up for all the rows with the specific activity selected. This removes the ability to filter the table by the activity but when I recreated it on my end, it would always show the value I anticipated in expected value even if some of the rows get filtered out of the table visualization.

9

Hi @DylanM

Thanks for your reply but after I tried it’s actually giving me value like this
image

which is not work as expected in my original Post
Can you help me giving the screenshot on your end so I can visualize how it looks like and see if it’s same as th expectation?
Thank you so much for the help

10

Hello @consultantcon , my apologies this is how it is working on my end as well. I was thinking this would satisfy your expectations.

Something that I think is important to mention, the tables in QuickSight are going to function as a whole. What you are trying to accomplish is not innate in regards to the visual. When you are including filters within a table element, there is not a way to avoid changing a specific column when the rest of the table is being impacted by it.

Some options that would be handled by the table in QuickSight:

  • To not utilize a parameter for Activity, and set the sumOver calculation to always sumOver({Value}, [{Activity}]) so the user could always see how the values add up among sports.
  • Set the sumOver calculation sumOver(Value, [${pActivity}], PRE_AGG) and allow the table to filter by the activity but not any of the other columns on the table so it does not alter the returned value for the expected column.
  • Or complete the task as I did above, and you could even alter the value for the else portion of the statement if you didn’t want the user to see 0. sumIf could be used as well for that option to not require an else, so it would be empty row values if the Activity does not match the parameter
11

Hi,

after checking with Quicksight support actually I just need to change PRE_FILTER to PRE_AGG

so the result that working for me is below
sumOver(ifelse(${pActivity} = {activity (short name)} , value, 0),PRE_AGG)

Thanks for the help!

1 Like
12

Hi @consultantcon , I am glad that is working! Thank you for the update.