Ignore a specific dimension filter within a set analysis

[stelozoya]

Hi All!!

I am currently working with the formula below to get an average selling price for the two products below. Location that equals Location 1 does not sell these products. Therefore I need an average from all other locations of their average selling price.

I would be filtered into location 1 and have selected a Year Month and Region filter. I want my set analysis to just ignore the location filter and let me add any other filter in the sheet. 

If I use the 1, it will ignore everything and not allow me to filter. Help, pretty please!

((SUM({1<[Product 1]={'Brand 1','Brand 2'}>} [Sales)])))

/

((SUM({1<[Product 1]={'Brand 1','Brand 2'}>} [Jobst])))

[Chanty4u]

Try this

SUM({$<[Product 1]={'Brand 1','Brand 2'}, Location -= {'Location 1'}>} [Sales])

/

SUM({$<[Product 1]={'Brand 1','Brand 2'}, Location -= {'Location 1'}>} [Jobst])

[stelozoya]

Hello! Thank you for replying, the issue with this is that it may not always be Location 1. Depending on the user they would be filtered into a different location. This would work if I only needed to exclude one location. Is there a way to make it more dynamic? I am sorry i did not clarify this before. 

[NadiaB]

Hi @stelozoya 

Our documentation for Set analysis is here:

https://help.qlik.com/en-US/qlikview/May2023/Subsystems/Client/Content/QV_QlikView/ChartFunctions/Se...

Hope it helps!

[Lena4]

Were you able to find the solution? I'm running into a similar issue. 

[simsa]

Try assigning the dynamic value into a variable.

SUM({$<[Product 1]={'Brand 1','Brand 2'}, Location -= {'$(vLocation)'}>} [Sales])

/

SUM({$<[Product 1]={'Brand 1','Brand 2'}, Location -= {'$(vLocation)'}>} [Jobst])

[simsa]

SUM({$<[Product 1]={'Brand 1','Brand 2'}, Location -= {'$(vLocation)'}>} [Sales])

/

SUM({$<[Product 1]={'Brand 1','Brand 2'}, Location -= {'$(vLocation)'}>} [Jobst])

[Gabbar]

Try this:-

SUM({$<[Product 1]={'Brand 1','Brand 2'}, Location = e()>} [Sales])

/

SUM({$<[Product 1]={'Brand 1','Brand 2'}, Location =e()>} [Jobst])

It will Exclude every selected location even if selected more than one but the negative point is that it will not show any data unless a Location value is selected because all are excluded without selection.