Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Qliksense drilldown

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,

1 Solution

Accepted Solutions
Not applicable
Author

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.

View solution in original post

1 Reply
Not applicable
Author

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.