8 Replies Latest reply: Dec 10, 2013 10:57 AM by Jeremiah Kurpat RSS

    rank in pivot table

      Hello all,

       

      I am trying to create a pivot table, showing the sales per country, customer id and customer name.

      In addition to the sales column, I want to have a column to show the rank of the individual sales.

      The outcome should look like following at the end:

       

      CountryCustomer IDCustomer NameSalesRank
      FranceCustomer ID1Customer 11002
      FranceCustomer ID2Customer 2503
      FranceCustomer ID3Customer 32001
      GermanyCustomer ID4Customer 44001
      GermanyCustomer ID5Customer 51002

       

      Country, Customer ID and Customer Name are dimensions, Sales and Rank are expressions.

       

      I am using an expression with the Rank function, which looks like following :HRank(SUM(Sales)).

      However, it returns the rank for each individual line. So at the end, it shows for each line rank "1" as (for some reason), it is not comparing the sales of that single combination Country+CustomerID+Customername vs the total country sales.

       

      I tried several different syntax but none of them worked.

       

      Does anyone have an idea, what needs to be changed here?

        • Re: rank in pivot table
          Juan Olivares

          as I see in your example QV is ranking your data by Country, but you want that your rank is overall the sales value.

           

          Try this expression:  Rank(Total Sum(Sales)).

           

          Int this case the TOTAL don't take care of what dimensions you have selected.

          • Re: rank in pivot table
            Jeremiah Kurpat

            For the rank function, it depends on what dimensions you have. Look at this text from Qlikview Help on the rank function:

             

            "If the chart is one-dimensional or if the expression is preceded by the total qualifier, the current column segment is always equal to the entire column. If the table or table equivalent has multiple vertical dimensions, the current column segment will include only rows with the same values as the current row in all dimension columns except for the column showing the last dimension in the inter field sort order."

             

            In your example, it is using the first two dimensions. This way every record is grouped with itself, so they all get a rank of 1. Remove Customer Name as dimension and put as expression, and if Sales is a dimension, change it to be sum(Sales). Hopefully this will resolve your issue.

             

            Hope this helps!

            • Re: rank in pivot table

              Ross,

               

              Coudl you try this:

               

              rank(aggr(sum(Sales), Country, CustomerID))

               

              AGGR creates a "table" dimensionned by Country and CustomerID. The rank returns the numer according to the rank it gets.

               

              Fabrice

                • Re: Re: rank in pivot table

                  Hi jerem1234 and Fabrice,

                   

                  jerem, you are correct. The rank function basically returns the ranking for each individual customer. So every customer gets the rank 1. Your hint with removing one column from the dimensions and adding it to the expression works indeed. I moved the customer ID column from the dimensions and added it to the expressions.

                   

                  However, there is still an issue with that solution.

                  I added another dimension (month dimension), to show the sales+rankings per month. The column was added on the x-axis.

                  But it then repreats the column customer id for every month (see picture below):

                   

                  QV TEst.jpg

                  Is there a way to create the ranking while using country, customer name, customer id and month as dimensions?

                  I tried several functions, including the one from Fabrice (using Aggr) but it doesnt work out yet (see in the attached file)