Drill Down issue overcome

    Problem Statement:

    While using drill down function of QlikView, I have identified if count of any dimension is 1 then Drill down skip that dimension and goes to a next level.

     

    For Example,

          We are having hierarchy called GEO which includes different level like Zone - > Country -> State -> Region … so on.

     

    Sample Data:

    SampleData.png

     

    Drill Down Group: Create your drill down group.

    DrillDown.png


    Chart Using Drill Group:  Add your dimensions and expression in straight table or your chart.

    ChartDimensionDrill.png

    ChartExpression.png


     

    Output using Drill Down Group:

    1) Drilling from Zone - > Country

    ChartDrill1.png

    2) Drilling from Country -> State

    ChartDrill2.png


    ChartDrill3.png

    Issue comes here, because of Country = FN is having 1 State so it is jumping to next dimensions. To overcome this problem I have tried to demonstrate by using same example.

     

     

    Solution

     

         1)  Create chart with all dimension which we have added in Drilldown Group as below screenshot.(Based on level of Hierarchy)

         ChartDimensionDrillSol.png

         2)  Add you expression

         3)  In dimension tab, follow the below steps.
               1. In a first dimension add below condition, based on your number dimension.

                  Ex. GetSelectedCount(Zone)=0 andGetSelectedCount(Country)=0 and GetSelectedCount(State)=0 and                   GetSelectedCount(Region)=0…so on.

     

              2. From Second dimension onwards, use below condition with minor changes for all.

                 Ex. If I am adding for Country

    GetSelectedCount(Zone)>0 and GetSelectedCount(Country)=0 and GetSelectedCount(State)<1 and GetSelectedCount(Region)<1

     

    If I am adding for next dimension of country which State then Before State condition should > 0 and after State it should be < 1(Excluding current dimension). For current dimension it should be = 0.

     

              3. For last dimension,current dimension condition will change from =0 to >=0.

                 Ex.GetSelectedCount(Zone)>0and GetSelectedCount(Country)>0 and GetSelectedCount(State)>0 and                    GetSelectedCount(Region)>=0

     

     

    Dry Run on Issue and Solution charts:

     

        1)  Initial View:

         ChartDrillSol1.png

        2)  Drill on First Level:

         ChartDrillSol2.png

            3)  Drill on Next level:

         ChartDrillSol3.png

     

        4)  Final Drill Down:

         ChartDrillSol4.png

     

    Hope this may solve or help !!

     

    Open for any suggestion or issue report.

     

    ****** Find Attached documents and application for reference ***************

     

    Regards,

    Ravi Balar