Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal will move to Qlik Community Oct 4. Temporary case outage Oct 3. READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Straight table Top 100

I have

Zone     Customer     Revenue

There are 10 zones; I want top 100 revenue generating customers from every zone that is I want 1000 customers in one straight table.

Is it possible??

1 Solution

Accepted Solutions
CELAMBARASAN
Partner
Partner

As per the issue stated, dimension limits work for you.

Please check the attached

View solution in original post

10 Replies
amit_saini
Master III
Master III

Aamir,

Can be achieved by Rank function, please see below a simple example:

Syntax:

=rank(sum(Investment),1,1)

AND

=aggr(if(Rank(sum(Investment))<=3,Investment),Investment) (For calculating Top 3)

AND

=if(rank(sum(Investment),1,1)=1,'# 1')

I have a data table which includes the following fields; SystemID, Timestamp.

I want to derive a new field in this table which ranks the Timestamp in ascending grouped by SystemID

I'd like to do this in the script.

  1. e.g.

SystemID, Timestamp,   [Rank]

00001, 01/01/2013 10:00,   1

00001, 01/01/2013 12:00,   2

00001, 01/01/2013 12:30,   3

00002, 01/01/2013 11:00,   1

00002, 01/01/2013 12:12,   2

00003, 01/02/2013 10:30,   1

Syntax:

LOAD

SystemID, Timestamp,

if(SystemID=previous(SystemID),peek('Rank')+1,1) as Rank

FROM ...mysource...

ORDER BY SystemID, Timestamp;



Thanks,

AS

Not applicable
Author

Can you please do it for my case.

Do I need to do changes in my script. Can't put rank in my straight table

amit_saini
Master III
Master III

Aamir,

You can do this either at script side or you can call this function as calculated expression also. For example purpose please see the attachment,

Thanks,
AS

Not applicable
Author

I have two dimensions

Not applicable
Author

Sample Data:

For this I want top 1 for each zone

ZoneCustomerRevenue
A11000
A21500
A32000
B41100
B51400
B62200
CELAMBARASAN
Partner
Partner

Check with the Dimension limits tab and as apply the settings as below

Capture.PNG.png

Not applicable
Author

Please reread the issue

CELAMBARASAN
Partner
Partner

As per the issue stated, dimension limits work for you.

Please check the attached

View solution in original post

Not applicable
Author

Oh yes It is working. The problem was I had put zone after customers