Want to ignore 1 filter in Quicksight

[krishna1]

Hi all,

I have 5 filter in pivot table. I want to ignore 1 filter effect in my chart. Other 4 filter should effect my chart.

Ask → Ignore only 1 filter and rest 4 filter should work as usual in pivot table.

Thanks

[ErikG]

Hi @krishna1
you can disable the one filter.
But why create a filter if you want to ignore it anyways.
BR

[krishna1]

Let me rephrase it.

I have a pivot table with 3 dimensions - region , country & product , and 2 KPIs - sales & item count. The dashboard has 3 filters - date , shipment mode & manager.

Formulae for item count is
Sum(item_count,[date, country])

The requirement is that the 2nd KPI - item count should be manupilated only according to selection of date&shipment mode and should not be affected by the selection of manager filter.

How to achieve this?

[David_Wong]

Hi @krishna1,

Take a look at this post. I think it’s a similar use case.

Pivot Rows and calculations

This post says the solution is to use the “in” function. I tried it and it seems to work: First you have to use parameters for your Country and Year filters. Then rewrite the calculated fields as follows: Month Total = sumOver(ifelse(in(Year, ${Year}) AND in(Country, ${Country}), Value, 0), [Month], PRE_FILTER) Oct Total = sumOver(ifelse(Month = “Oct” AND in(Year, ${Year}) AND in(Country, ${Country}), Value, 0), , PRE_FILTER) [image]

[krishna1]

The use case is that we need to create a calculation to find item count fixed for team name and week end date . cal1 → sumOver({Item Cnt},[{Team Name},{Week End Date}], PRE_FILTER) . it is giving the correct results as needed. Now I further want to use this calculated column cal1 in another calculated so column let’s say cal2 in which I want that the output should be manipulated by selection in the filter - {Subtype} but should not be affected by {Region_Name} filter.
I have written the following formulae → cal2 → sumOver(ifelse(in({Subtype },${Subtype_para}),[{cal1}],0), [ {Region_Name}], PRE_FILTER). but this is not working as expected as the value is getting changes according to selection in region . please help with the issue.

[ArunSanthosh]

Hi @krishna1 ,

In your second calculation, you have set the partition attribute to be region ie - it is calculated at the region level and then summarized. So, region selection will impact this calculation. Also, keep in mind that your first sumOver calc is getting the summarized Item count for each team-week combo and logically stamping it on all records for that team-week combo. So, just adding another summarization on top of it can cause data to be double counted based on the nature of your dataset.

If you need further assistance with this problem, please create a sample analysis in Arena (using mock data) showing your problem state and add details of what values you are expecting to see. This will make it easier for community member to help you get to a solution.

Read more on Arena here - Question & Answer - Amazon QuickSight Community

Regards,
Arun Santhosh
Pr QuickSight SA