Populating Dimension Filter Using Only Aggregate Function

[garvitgupta96]

Hey Guys,

Need your help to solve this problem

I have two Dimensions namely : Country and States

i am using the Country dimension as a variable and making its selection possible using a variable drop down.  Also i have another dimension filter of states where i am using Qlik Native filter.  What i want to do is, whenever i am selecting India in my country variable, i want it to show me only relevant states and if US then the states of US.

Country	State
India	Delhi
India	Mumbai
India	Tamil Nadu
India	Rajasthan
India	Kolkata
US	Calafornia
US	Texas
US	Florida
US	Washington

for country i am using $(vCountry) and for State i am using [State] dimension as a native filter. 

to calculate this i used Only function :

=Aggr(Only({<product={'$(vBU)'}>}[SP Name]), [SP Name])

But what happens is that if i select a country India, it shows me all the states of India and when i select a state, except the selected state all the other Indian States disappear. All i want is to retain those indian states as well.

Kind of an urgent and important problem statement for me. Please help out. 

[lironbaram]

hi 

why don't you use the native filter for both country and state 

it make that much more sense , and it will allow you to not waste time on this matter 

[garvitgupta96]

@lironbaram there was an ask for it and to maintain a consistency, but the business requirement changed recently and converting it to native filter would be a lot of hardowork

[garvitgupta96 · Accepted solution]

well i found the solution to do this. 

you could use the below given expression to populate that

=Aggr(Only({1<product={'$(vBU)'}>}[SP Name]), [SP Name])

Well definitely thats simple solution to a simple question.