3 Replies Latest reply: Sep 1, 2014 7:41 AM by James Dixon RSS

    Using Loop/Peek/Concatenate to Rank Profit For Each Region

      Hello all,

       

      I am having some trouble wrapping my head around the code needed to do the above questions. So essentially what I have is a million or so rows of the form below: I have loaded in the data Product, Region and Profit and essentially end up with a table something like the below (just with a lot more rows).

       

      ProductRegionProfit
      OrangesSR663.9041
      LemonsTR510.8336
      ApplesSR417.4877
      ApplesSR546.6837
      LemonsSR735.3071
      OrangesTR504.7666
      OrangesTR658.6476
      LemonsLN249.9038
      ApplesLN654.5401
      ApplesLN15.58008
      LemonsLN298.2942
      OrangesLN619.8974
      ApplesRU792.2068
      LemonsRU899.0773
      OrangesRU526.7167

       

      What my overall aim is to rank each region in terms of the profit they have made. I imagine I will need to use loop and peek etc to ensure that I have a rank for each region, not just ranking over the whole set of data, so I want a ranking of LN going from 1->5 and then a rank from RU going from 1->3. Not a rank going from 1->16.

       

      Then I can create a table in the qlikview with expressions which will be able to show me data like this (please note the below table does not correspond to data in the above table):

       

      201120122013
      LN1st5th9th
      SR10th14th23rd
      TR3rd2nd4th
      RU7th4th6th

       

      Thank you all in advance for your help.

       

      James

        • Re: Using Loop/Peek/Concatenate to Rank Profit For Each Region
          Srikanth P

          Hi James, Please Post the you entire data model ? I didn't see the Year field on mentioned data.

           

          I believe, you can directly achieve what do you need with Pivot table.

            • Re: Using Loop/Peek/Concatenate to Rank Profit For Each Region

              Unfortunately I am unable to share the Qlikview document, but essentially there is simply another column that just has a date of sale on it.

               

              In a normal chart (not pivot table) I can get the profit in the table that is required i.e. as:

               

              201120122013
              LN900

              510

              911
              SR1000140233
              TR450270425
              RU650485675

               

              I get this by having my region as a dimension, and then I have three expressions with labels [Sale Date], ([Sale Date]-1) and ([Sale Date]-2) (i plan on being able to click on product and then on 2014, to compare the product sales for 2014, 2013 and 2012) against the other products. The expression in the chart is as follows:

               

              sum({<[Sale Date] = {"$(=([Sale Date]-1))"}>} $(vProfit)) where v(profit) is my profit calculating variable. This will give me the above table which is all well and good but I want to rank the products position out of the whole region for all the products in that region.

               

              On another note this works fine if i get rid of the regions, and just use product as a dimension, then have the following expressions:

               

               

              Aggr(Rank(TOTAL sum({<Product=,[Sale Date]={"$(=([Sale Date]-1))"}>} $(Profit)),4),Product))

               

              This will work for the single row and I get the correct rankings over all the products. The problem is basically having to introduce the region which then messes up the rankings. Hence the possible need to loop over each region and rank per region. I realise its hard without the actual file but any ideas on how to go about this?

               

              What would be cool is to have a kind of peek, loop formula in the script so that it would list all LN and then rank, then all SR and then rank etc.

              • Re: Using Loop/Peek/Concatenate to Rank Profit For Each Region

                Hi Dathu,

                 

                I have attached a test document. Still stuggling away with this! The test document works but takes a long time to load. Can you or anyone else think of a better way to do this? I also still havent got the exact table I am looking for.I am looking so that you can click on Coke, and 2014 (as in the test qvd file and you get the following):

                                         2013     2014

                Coke     LN          1            2

                            SG          1            1

                            FR           1            1

                            NY           2            1

                 

                Any help would be greatly appreciated!

                 

                James