8 Replies Latest reply: Jul 22, 2011 6:18 AM by Sravan Puppala RSS

    Distinct count Per Group of Dimensions

    Sravan Puppala

      Hi Guys,

       

      Seems to be simple but I am not getting this one.

       

      I have a chart with 3 Dimensions and 1 Expression

       

      The Three dimensions are

       

      1) Processchain

      2) ProcesschainText

      3) Client

       

      and the expression is Count(distinct [Composite role/User_UNAME])

      The chart Looks as below and the Field [Composite role/User_UNAME] is as given below.

      chartRisk.jpg

       

      Now my problem is Here if I select a User->[Composite role/User_UNAME ] as below it shows 1 for all the Processchains.

      chartRisk1.jpg

       

       

      Actually I need the count as 1 as the Processchains are almost same. For Ex here.. 91170_01 to 91170_08 should be considered as 91170 and count should be for the above user only 1 for all these Process chains and not individually 1 per each processchain as above in diagram.

       

      Any help?

       

      Thanks
      Sravan

        • Distinct count Per Group of Dimensions
          Stefan Wühl

          Hi Sravan,

           

          how do you decide that process chains are almost the same? If you decide that using the process chain number, first part, disregarding  the part after "_", you could maybe build a new process chain ID from that just to count the "really" distinct process chains.

           

          Regards,

          Stefan

            • Re: Distinct count Per Group of Dimensions
              Sravan Puppala

              Hi Stefan,

               

              Thanks for your answer.

              I decided that the Processchains are same based on the text of Processchain. See the previous attached screenshot.

              I did the splitting of the Field using Subfield in the following way

               

              subfield(Processchain,'-',1) as Processchain1

              subfield(Processchain,'-',2) as Processchain2

               

              Now I need to use this Processchain1 to Remove the users which are redundant for 91170

               

              for Example For Processchain1= 91170, the User QMN_SUB is present in everyrow. It must be only once if the processchain1 is same( as in the attached case).

               

              chartRisk2.jpg

              Can this be done in chart or it must be done in Script?

               

              Regards
              Sravan

                • Re: Distinct count Per Group of Dimensions
                  Stefan Wühl

                  Hi Sravan,

                   

                  so you want to display only one row? I think you got this result if you remove Processchain and Processchain_2 from dimensions and do a count(distinct ProcessChain_1) as expression.

                   

                  If you want to show also Processchain and Processchain_2 that might be a bit more tricky.

                  Could you describe how the table should look like then, i.e. you want the expression to be shown only once. In which row then?

                  It might be possible to check the dimension level an calculate the expression conditional or as subtotal.

                   

                  Just some ideas.

                   

                  Stefan

                    • Re: Distinct count Per Group of Dimensions
                      Stefan Wühl

                      Another idea,

                       

                      if the second part of the processchain always follow the scheme _01, _02, _03 and always starts with _01,

                      then you might do something like

                       

                      count(if(processchain2='01', whateveryoucount, 0)

                       

                      you can force QlikView to show also 0 values in presentation tab of properties, I think.

                       

                      But not sure what you are after.

                       

                      Stefan

                      • Re: Distinct count Per Group of Dimensions
                        Sravan Puppala

                        Hi Stefan,


                        Thanks for answer.

                        swuehl schrieb:

                         

                        Hi Sravan,

                         

                        so you want to display only one row? I think you got this result if you remove Processchain and Processchain_2 from dimensions and do a count(distinct ProcessChain_1) as expression.

                        It is not about row. what I want is, if the Processchain is same, For example 91170 I get a count of user QMN_SUB as 8 (see the fig below)

                        chartRisk2.jpg

                        This User must be counted only once and not 8 times as in my chart now. Thats my problem. I hope I am clear now.

                         

                        Regards

                        Sravan