4 Replies Latest reply: Mar 19, 2013 1:41 PM by Balaji N RSS

    Finding nodal median in QlikView

    Balaji N Mar 19, 2013 8:08 AM

      I have a query regarding finding median value in a hierarchical dimension in QlikView:

      I have a following hierarchical dimension in QlikView. (Dimension Name : VendorSubLocation, Vendor is the parent and Location is the child).

      Assume the following values (Dimension names and the expression value):

       

                                            Rate

      IBM                                  

           USA                            12

           UK                              13

           Australia                      14

      ATK

           USA                             13

           UK                               12

           SA                               16

       

      Corresponding to each Level 1 of VendorSubLocation dimension, I want to find out median value. That is the output should be

       

      IBM - 13

      and ATK - 13

       

      Can you please suggest a method how this can be implemented in the load script?

        • Re: Finding nodal median in QlikView
          Gysbert Wassenaar
          Gysbert Wassenaar Mar 19, 2013 8:25 AM (in response to Balaji N)

          Use the median function. You'll need a group by clause if you want to calculate this in the script.

           

          load Vendor, median(Rate)

          from ...somewhere...

          group by Vendor;

            • Re: Finding nodal median in QlikView
              Balaji N Mar 19, 2013 9:10 AM (in response to Gysbert Wassenaar )

              Hi Gysbert,

              There is an additional complication. Corresponding to each vendors there are multiple transactions. So consider the following situation:

                                                    Rate          # of transactions

              IBM                                 

                   USA                            12                    10

                   UK                              13                     20

                   Australia                      14                    61

               

              Your solution will return 14 as the median value since Rate corresponding to  45th/46th transaction is 14. However, I want the median value as 13.

                • Re: Finding nodal median in QlikView
                  Gysbert Wassenaar
                  Gysbert Wassenaar Mar 19, 2013 9:31 AM (in response to Balaji N)

                  Then first do a load distinct Vendor, Rate ...from... etc so you get the distinct Rate values

                    • Re: Finding nodal median in QlikView
                      Balaji N Mar 19, 2013 1:41 PM (in response to Gysbert Wassenaar )

                      That still does not seem to solve my problem. I think I should rephrase my issue and mention the data fields present in the fact so that it becomes simpler.

                       

                      The input file has following format:

                       

                      Company      Geography            Rate             Hours

                      IBM                    USA                 11                  1

                      IBM                    USA                 12                  1

                      IBM                    USA                 13                  1

                       

                      I calculate the rate for "Company-Geography" combination at load time by grouping by "Company-Geography" combination. ( I have a "Company-Geography" concatenated field in the input file). Lets call this effective rate.

                      What I want to do is for a given company, I want to find the geography corresponding to median rates. There are multiple transactions for "Company-Geography" combination and effective rate can be same for any two "Company-Geography" combination (which is one of the reasons why i am concerned about using Distinct).