Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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.
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.