Qliksense drilldown

[#1]

Hello Everyone,

I have a drill down dimension State-City. The measure is average population.

Now I create different bookmarks with different states and cities selected. The average population of a State should not change based on the cities selected. For example, if Alabama has 20 cities and I choose 10 in the bookmark, then average for Alabama should for 20 cities not 10. But when I drill into Alabama, I need to see only 10 cities average. Need help with  the expression.

Thanks,

[#2 · Accepted solution]

I figured it out. It's 2 step solution. The expression needs to be

if(GetSelectedCount(Sate)=1,

avg({<Year=, Month=,Quarter=,Date_Key={">=$(=Num(YearStart($(vMaxDateNum))))<=$(=$(vMaxDateNum))"}>}population)

,

avg({<Year=, Month=,Quarter=,City, Date_Key={">=$(=Num(YearStart($(vMaxDateNum))))<=$(=$(vMaxDateNum))"}>}population)

)

Create Master dimension

IF(AGGR(<my expression>, my dimension), my dimension, null())

In my case I needed Drill down dimension [State-City].

State

IF(AGGR(if(GetSelectedCount(State)=1,

if(GetSelectedCount(Sate)=1,

avg({<Year=, Month=,Quarter=,Date_Key={">=$(=Num(YearStart($(vMaxDateNum))))<=$(=$(vMaxDateNum))"}>}population)

,

avg({<Year=, Month=,Quarter=,City, Date_Key={">=$(=Num(YearStart($(vMaxDateNum))))<=$(=$(vMaxDateNum))"}>}population)

)

, State),State, null())

City

IF(AGGR(if(GetSelectedCount(State)=1,

if(GetSelectedCount(Sate)=1,

avg({<Year=, Month=,Quarter=,Date_Key={">=$(=Num(YearStart($(vMaxDateNum))))<=$(=$(vMaxDateNum))"}>}population)

,

avg({<Year=, Month=,Quarter=,City, Date_Key={">=$(=Num(YearStart($(vMaxDateNum))))<=$(=$(vMaxDateNum))"}>}population)

)

, City),City, null())

The above expression will suppress any cities that were not selected in the bookmark.