topic Re: Create Rank based on Total % Values in QlikView

Create Rank based on Total % Values

dmohanty — Fri, 12 Dec 2014 19:14:01 GMT

SupplierSpend:

load * inline

[

SupplierID, Spend

101, 20k

201,35k

301,40k

401,18k

501,22k

601,31k

701,42k

801,25k

901,39k

];

Hi All,

Above is the raw data set.

REQUIREMENT:

I need to create a Chart, that would show the Groups of the Supplier based on some criteria. Below is the criteria:

Group 1: All Supplier ID that consists of Top 80% of total Spend values

Group 2: Next 15%

Group 3: Last 5%.

SO, the chart may look like this below (ignore the calculation shown below; just a representation). Please help me in this.

Group	SupplierID	Rank
A	701	1/4
	301	2/4
	901	3/4
	201	4/4
B	601	1/3
	801	2/3
	501	3/3
C	101	1/2
	401	2/2

Regards!

Re: Create Rank based on Total % Values

dmohanty — Fri, 12 Dec 2014 21:54:35 GMT

Hi All,

Any help please?

Re: Create Rank based on Total % Values

MK_QSL — Fri, 12 Dec 2014 22:43:43 GMT

Can you please confirm that the output (result) you are expecting is the right one?

Re: Create Rank based on Total % Values

jer_2011 — Sat, 13 Dec 2014 00:24:40 GMT

Hola,

te paso este ejemplo espero te sirva.

saludos.

Script:

Re: Create Rank based on Total % Values

dmohanty — Mon, 15 Dec 2014 01:55:05 GMT

Hi Manish,

Yes this the way I wanted (may be some weird requirement ).

Also, I want the a Text Object to be present where when we select any Supplier/Group, the Rank would display in the Box.
For example, if I select SupplierID = 201 (Group A), the value in the Text Object should display as 4/6.

Is it possible?

Re: Create Rank based on Total % Values

dmohanty — Mon, 15 Dec 2014 01:55:25 GMT

Hi Gerardo,

Many thanks for this. This is much appreciated!

Also, I want a Text Object to be present where when we select any Supplier/Group, the Rank would display in the Box.
For example, if I select SupplierID = 201 (Group A) from the list box, the value in the Text Object should display as 4/6.

Now if I add your Rank expression in a separate Text Object, by default '1/' is coming. So whatever Supplier Id I select, its showing 1/1. But I was looking for real rank. Example: For 601 selection, it would be 5/6 or for 101 selection, it would be 2/2.

Is it possible? Please help some more.

Re: Create Rank based on Total % Values

jer_2011 — Mon, 15 Dec 2014 16:49:16 GMT

Hola,

modifique el código y te mejore la aplicación, espero te sirva.

saludos

SupplierSpend_TMP:
load * inline

[
SupplierID, Spend

101, 20

201,35

301,40

401,18

501,22

601,31

701,42

801,25

901,39
];

NoConcatenate

SupplierSpend_TMP2:
load SupplierID,
Spend
Resident SupplierSpend_TMP;

left join

LOAD SUM(Spend) AS TotalSpend
RESIDENT SupplierSpend_TMP;
drop table SupplierSpend_TMP;

NoConcatenate

SupplierSpend_TMP3:
load SupplierID,
Spend,
TotalSpend,
Spend / TotalSpend as percentage%,

if(isnull(peek('accumulated'))=-1,( Spend / TotalSpend),Peek('accumulated') +( Spend / TotalSpend)) as accumulated,

if( (if(isnull(peek('accumulated'))=-1,( Spend / TotalSpend),Peek('accumulated') +( Spend / TotalSpend))) <=.80,'A',
IF( (if(isnull(peek('accumulated'))=-1,( Spend / TotalSpend),Peek('accumulated') +( Spend / TotalSpend))) >=.95, 'C', 'B')) AS Group

Resident SupplierSpend_TMP2
order by Spend desc;
drop Table SupplierSpend_TMP2;

NoConcatenate

SupplierSpend:
load SupplierID,
Spend,
TotalSpend,
percentage%,
accumulated,
Group,

IF(isnull(Peek(Group))=-1,1, if(Peek(Group)= Group, 1 + Peek('Rank'), 1) ) AS Rank

Resident SupplierSpend_TMP3
order by Spend desc;

left Join(SupplierSpend)

load Group,
Count(SupplierID) as CountGroup

Resident SupplierSpend_TMP3
Group by Group;

drop Table SupplierSpend_TMP3;

Re: Create Rank based on Total % Values

dmohanty — Tue, 16 Dec 2014 20:43:35 GMT

Hi Gerardo,

I much appreciate your efforts on this and this is what I wanted. I learnt the concept from you.

Marking this thread as correct. Thank you very much for the help!

However I have a small requirement change now:

Earlier every single SupplierID has a single Spend value (your Inline Table). Now for each SupplierID, we have 12 months of Spend Data. So now, for each SupplierID, there will be different Group and Rank value (after calculation in load script) based upon each month.

I tried adding the new fields in your existing script, but only Group C is coming as outcome. Now after adding the 12 months of data for each SupplierID, I need a Line Graph to be seen like this below:

I am attaching my new set of Raw Data (Only Supplier Name and Month field has been added.)

Re: Create Rank based on Total % Values

jer_2011 — Tue, 16 Dec 2014 22:39:53 GMT

Hola,

te paso Otro EJEMPLO Es Lo Mejor Que Pude HACER,

si puedes Marcar este enlace Como Útil, ya Que vale doble

Re: Create Rank based on Total % Values

dmohanty — Wed, 17 Dec 2014 04:36:43 GMT

Hey Gerardo,

Holy Cow. You are genious. Slowly I am understanding and getting the solution. Many many thanks again for this.

But I have a concern here:

After loading the new data, how the Rank and CountGroup is calculated? For EXAMPLE: For ID = 301 from Group = A, Rank of Jan is 3/3 , Feb is 1/3 and so on.

On what basis the Rank is 3,1...., out of CountGroup 3 ?

As ID = 301 from Group = A has 8 months, I was expecting the CountGroup to be 8 and we would have 8 Ranks.

Could you please help me understanding this concept?

Re: Create Rank based on Total % Values

jer_2011 — Wed, 17 Dec 2014 16:00:38 GMT

Hola,

Gracias por tus elogios, espero me puedas entender, el Rank lo tomo como base de tu primer solicitud, esta el ejemplo en el mismo qvw que te mande, solo dale expandir ahí podrías observar como esta armado, esto quedaría así

por lo tanto queda asi

SupplierID = 301

Month Group Rank

Jan A 3 / 3

Feb A 1 / 3

Mar A 2 / 3

Apr A 3 / 3

espero te sea de utilidad, y se necesitas otro tipo de Rank, solo indícame y lo intentamos

saludos

Re: Create Rank based on Total % Values

dmohanty — Fri, 19 Dec 2014 00:10:35 GMT

Hey Gerardo,

Greetings! Yes I can understand you (though by Google translator ).

Apologies for the delay in reply, as I didn't get a chance to work/look on this. Many more thanks again for a detailed explanation to my query. I got your point of solution now. You have the CountGroup based on the no. of Groups for each month.

However I needed in a reverse way. My need was the CountGroup should be based on Months.

For example,

ID = 301 from Group = A has 8 months, I was expecting the CountGroup to be 8 and we would have 8 Ranks.

Hope you understood. Is it possible?

Thanks in advanced!

Re: Create Rank based on Total % Values

jer_2011 — Fri, 19 Dec 2014 19:53:31 GMT

Hola nuevamente

te paso nuevamente el ejemplo, la idea es entenderle a la función peek()

saludos espero te sirva