1 Reply Latest reply: Apr 18, 2017 1:01 PM by Anusha Vijayakumar RSS

    Qliksense drilldown

    Anusha Vijayakumar

      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,

        • Re: Qliksense drilldown
          Anusha Vijayakumar

          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.