Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

3 Replies
Not applicable
Author

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.

Not applicable
Author

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.

Not applicable
Author

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