topic Re: Sales of Top Rank Company in App Development

Sales of Top Rank Company

Anonymous — Fri, 07 Dec 2018 06:05:58 GMT

I have a data set like the below data set which has State wise Company Sales.

State	Company	Sales
A	C1	500
B	C1	600
C	C1	4000
A	C2	450
B	C2	800
C	C2	950
A	C3	1000
B	C3	2000
C	C4	3500

The requirement is to find Rank 1 Company in State A,B,C in a pivot table.

The desired output is like.

STATE	Rank 1 Comapny	Sales of Rank1 Company
A	C3	i.e. sales of C3 = 1000
B	C3	i.e. sales of C3 = 2000
C	C1	i.e. sales of C1 = 4000

Please help, thanks in advance.

Re: Sales of Top Rank Company

tresB — Fri, 07 Dec 2018 06:11:42 GMT

Dimension 1: State

Dimension 2: Company

Exp:
If(rank(Sum(Sales))=1, Sum(Sales))

Re: Sales of Top Rank Company

Anonymous — Fri, 07 Dec 2018 06:18:29 GMT

In my case i have only State as dimension in pivot table so this expression will only return Sales for Rank 1 State. so this is not fulfilling the requirement

Re: Sales of Top Rank Company

tresB — Fri, 07 Dec 2018 06:22:54 GMT

This?

Aggr(If(rank(Sum(Sales))=1, Sum(Sales)),State,Company)

Re: Sales of Top Rank Company

shiveshsingh — Fri, 07 Dec 2018 06:25:38 GMT

PFA

Regards

Shivesh

Re: Sales of Top Rank Company

Anonymous — Fri, 07 Dec 2018 06:31:57 GMT

please can you provide me the logic as i dont have qlikview to open this file.

Re: Sales of Top Rank Company

shiveshsingh — Fri, 07 Dec 2018 06:48:10 GMT

PFB

Script:

T:
LOAD * INLINE [

State, Company, Sales
A, C1, 500
B, C1, 600
C, C1, 4000
A, C2, 450
B, C2, 800
C, C2, 950
A, C3, 1000
B, C3, 2000
C, C4, 3500
];

Dimension : -State, Company

Expression : if(rank(Sales)=1,Company)

In presentation Tab, please hide Dimension Company

Re: Sales of Top Rank Company

shiveshsingh — Fri, 07 Dec 2018 06:48:44 GMT

Re: Sales of Top Rank Company

tresB — Fri, 07 Dec 2018 06:52:27 GMT

If you are interested in the name of the company try:

FirstSortedValue(Company, -Aggr(sum(Sales), State, Company))