Need to ignore filters and create a static formula excluding selections

[rm1]

Hi Everyone

I should write a formula to calculate the sales for a specific Level of product (I have 4 Levels A,B,C,Unclassified). I need to make the result static (if some filter would be selected, the result of the KPI should remain the same).

I have tried the following formula to calculate the percentage of sales for levels A and B excluding the unclassified. Mathematically I'm simply doing: (A+B) / (A+B+C).

sum({Level={'A', 'B'}> - <Level={'Unclassified'}>} [sales]) / sum({ - < Level ={'Unclassified'}>} total [sales])

now, my problem is that if I try to select the product Level C inside the filters, the result changes because the formula doesn't disregards the selections. How can I do to make the result Static?

Thanks in advance for any hint!

[Saif]

Try this sum({1<Level={'A','B'}>} sales)/sum({1<Level={'A','B',C}>} sales).

This expression will only calculate the sales for the Levels which we have mentioned in the set analysis.

Identifier 1 is used to disregard any selections. Go to this link to read about identifiers and other parts of set analysis.

https://help.qlik.com/en-US/sense/February2020/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/SetAn...

[sunny_talwar]

For your numerator, if Level is A or B, how will it be Unclassified at the same time? I think you can just use this for numerator

Sum({1<Level = {'A', 'B'}>} [sales])

For denominator, may be try this

Sum({1<Level = e({1<Level  = {'Unclassified'}>})>} TOTAL [sales])

[rm1]

sorry, my fault.

I forgot to specify that i have some basic filters (e.g. Country, Year). So I can't use the formula you mentioned because it will disregard also the basic filters that I need.

My original formula:

sum({$<Level={'A', 'B'}> - <Level={'Unclassified'}>} [sales]) / sum({${} - < Level ={'Unclassified'}>} total [sales])

Should become static: if someone make some filter on tier and select product level C, the result should not change (using my original formula, total would become grater than 100%)

[MikeW · Accepted solution]

You can still use 1< to ignore all selections and then for any fields that you don't want to ignore selections, add them back to your sets using a P() function. 

eg: {1<Level = {'A','B'}, Country = P(Country), Year = P(Year)> - 1<Level = {'Unclassified'}, Country = P(Country), Year = P(Year)>}

P(Field), when not specifying a set analysis implies P({$}Field), which gets you the set of current selections for that field.

[rm1]

this is exactly what I need. thanks a lot!