18 Replies Latest reply: Aug 6, 2015 4:43 AM by Ruben Marin RSS

    Set analysis

    Satish Pawar

      Count({<param_name={'TEMP'}, param_value={'<=-1'}>} param_value)/

      Aggr(Nodistinct count({<param_name={'TEMP'}, param_value={'<=-1'}>}param_value),branch_type)

       

      This expression gives me 100% Stack bar chart which i have required.

       

      but when i drill down for branch_name it won't show 100% for branch_name.

       

      what Changes need to be put in expression?

       

       

      In Qlikview we can add the whole group of drilled down dimensions at branch _type

       

      how can workout for this in Qlik sense?

       

      please suggest.

        • Re: Set analysis
          Sunny Talwar

          May be try this:

           

          Count({<param_name={'TEMP'}, param_value={'<=-1'}>} param_value)/

          Sum(Aggr(Nodistinct count({<param_name={'TEMP'}, param_value={'<=-1'}>}param_value),branch_type, branch_name))

            • Re: Set analysis
              Satish Pawar

              Hi Sunny,

               

              it won't work..even it shows % Which is less than 100% in first level of drill down which is incorrect.

              it should shows 100% for all levels.

               

              coommm.PNG

                • Re: Set analysis
                  Sunny Talwar

                  Makes sense. How are you drilling into a different dimension as there is no groups available in Qlik Sense (if I am not wrong)? Are you using variable to switch from one dimension to another?

                    • Re: Set analysis
                      Satish Pawar

                      No i'm not using variable.

                      there is one option from where we can create group for drill down and also manipulate in Chart.

                      Qlik Sense - Drill-down Group Dimension (video)

                       

                      but there is no option to add such group into expression.

                      its quite strange

                        • Re: Set analysis
                          Sunny Talwar

                          I guess I understand the issue, but I am new to Qlik Sense as well. May be somebody from Qlik Tech might be able to help you better.

                           

                          Best,

                          Sunny

                            • Re: Set analysis
                              Satish Pawar

                              here

                              i'm trying to get the row wise count at denominator.

                              if you have some new way.

                               

                              please suggest.

                                • Re: Set analysis
                                  Ruben Marin

                                  Hi, just guessing, maybe:

                                  If(Count(Distinct branch_name)>1,

                                   

                                  Count({<param_name={'TEMP'}, param_value={'<=-1'}>} param_value)/

                                  Sum(Aggr(Nodistinct count({<param_name={'TEMP'}, param_value={'<=-1'}>}param_value),branch_type),

                                   

                                  Count({<param_name={'TEMP'}, param_value={'<=-1'}>} param_value)/

                                  Sum(Aggr(Nodistinct count({<param_name={'TEMP'}, param_value={'<=-1'}>}param_value), branch_name))


                                  )


                                  I'm trying to check where the dimension is branch_name or branch_type, and each has his aggr. If some branch_type only has 1 branch_name there should be no difference between using branch_type or branch_name for aggr().

                                    • Re: Set analysis
                                      Satish Pawar

                                      Hi Ruben,

                                       

                                      i'm using group of dimensions for drilled down purpose.

                                      at first level of branch_type it shows 100% which is correct but at second level it is not showing 100% that is not correct.

                                      i'm trying to get 100% on each level of chart from my expression

                                       

                                      Each branch_type has a branch_names

                                       

                                       

                                      1)

                                      first.PNG

                                      2)

                                      coommm.PNG

                                        • Re: Set analysis
                                          Ruben Marin

                                          Hi Satish, maybe this expression:

                                          If(Count(Distinct branch_name)>1,

                                          Count({<param_name={'TEMP'}, param_value={'<=-1'}>} param_value)

                                          /Sum(Aggr(Nodistinct count({<param_name={'TEMP'}, param_value={'<=-1'}>}param_value),branch_type),

                                           

                                          Count({<param_name={'TEMP'}, param_value={'<=-1'}>} param_value)/

                                          /Sum(count(TOTAL {<param_name={'TEMP'}, param_value={'<=-1'}>}param_value))

                                          )


                                          If doesn't works please upload a sample to make tests.

                                            • Re: Set analysis
                                              Satish Pawar

                                              Hi Ruben,

                                               

                                              its not working.

                                               

                                              here i'm attached sample QVF to test.

                                               

                                              thanks in advance.

                                                • Re: Set analysis
                                                  Ruben Marin

                                                  Hi Satish, this expression worked:

                                                  If(Count(total DISTINCT branch_type)>1,

                                                  Count({<param_name={'TEMP'}, param_value={'<=-1'}>} param_value)

                                                  /Aggr(Nodistinct count({<param_name={'TEMP'}, param_value={'<=-1'}>}param_value),branch_type),

                                                   

                                                  Count({<param_name={'TEMP'}, param_value={'<=-1'}>} param_value)

                                                  /Aggr(Nodistinct count({<param_name={'TEMP'}, param_value={'<=-1'}>}param_value),branch_name)

                                                  )

                                                   

                                                  You can add more "levels":

                                                  If(Count(total DISTINCT branch_type)>1,

                                                  Count({<param_name={'TEMP'}, param_value={'<=-1'}>} param_value)

                                                  /Aggr(Nodistinct count({<param_name={'TEMP'}, param_value={'<=-1'}>}param_value),branch_type),

                                                   

                                                  If(Count(total DISTINCT branch_name)>1,

                                                  Count({<param_name={'TEMP'}, param_value={'<=-1'}>} param_value)

                                                  /Aggr(Nodistinct count({<param_name={'TEMP'}, param_value={'<=-1'}>}param_value),branch_name),

                                                   

                                                  Count({<param_name={'TEMP'}, param_value={'<=-1'}>} param_value)

                                                  /Aggr(Nodistinct count({<param_name={'TEMP'}, param_value={'<=-1'}>}param_value),loc_code),

                                                  ))

                                                    • Re: Set analysis
                                                      Satish Pawar

                                                      Hi Ruben,

                                                       

                                                      Thanks for your great efforts.

                                                      But have you checked it for loc_code and loc_type.

                                                      beacuse i think its not working for them.

                                                       

                                                      i used following expression

                                                       

                                                      If(Count(total DISTINCT branch_type)>1,

                                                      Count({<param_name={'TEMP'}, param_value={'<=-1'}>} param_value)

                                                      /Aggr(Nodistinct count({<param_name={'TEMP'}, param_value={'<=-1'}>}param_value),branch_type),

                                                       

                                                      If(Count(total DISTINCT branch_name)>1,

                                                      Count({<param_name={'TEMP'}, param_value={'<=-1'}>} param_value)

                                                      /Aggr(Nodistinct count({<param_name={'TEMP'}, param_value={'<=-1'}>}param_value),branch_name),

                                                       

                                                       

                                                      If(Count(total DISTINCT loc_type)>1 ,

                                                      Count({<param_name={'TEMP'}, param_value={'<=-1'}>} param_value)

                                                      /Aggr(Nodistinct count({<param_name={'TEMP'}, param_value={'<=-1'}>}param_value),loc_type),

                                                       

                                                       

                                                      If(Count(total DISTINCT loc_code)>1 ,

                                                      Count({<param_name={'TEMP'}, param_value={'<=-1'}>} param_value)

                                                      /Aggr(Nodistinct count({<param_name={'TEMP'}, param_value={'<=-1'}>}param_value),loc_code)))))

                                                       

                                                       

                                                      please check

                                                        • Re: Set analysis
                                                          Ruben Marin

                                                          You only need the one I posted:

                                                          If(Count(total DISTINCT branch_type)>1,

                                                          Count({<param_name={'TEMP'}, param_value={'<=-1'}>} param_value)

                                                          /Aggr(Nodistinct count({<param_name={'TEMP'}, param_value={'<=-1'}>}param_value),branch_type),

                                                           

                                                          If(Count(total DISTINCT branch_name)>1,

                                                          Count({<param_name={'TEMP'}, param_value={'<=-1'}>} param_value)

                                                          /Aggr(Nodistinct count({<param_name={'TEMP'}, param_value={'<=-1'}>}param_value),branch_name),

                                                           

                                                          Count({<param_name={'TEMP'}, param_value={'<=-1'}>} param_value)

                                                          /Aggr(Nodistinct count({<param_name={'TEMP'}, param_value={'<=-1'}>}param_value),loc_code),

                                                          ))

                                                           

                                                          I don't see any selections where this doesn't work, but there are many possibilities to select in the chart, tell me with wich selections this doesn't works.

                                                  • Re: Set analysis
                                                    Anand Chouhan

                                                    Hi,

                                                     

                                                    With your expression use the Dual function as well as in your expression that may solve your problem.

                                                     

                                                    Regards,

                                                    Anand