Flag for Top 50 records in Report builder

[nihhalmca]

Hi All,

I want to generate flag for top 50 records for Revenue filed in report builder (Dynamic chart).

I have to show flag for top 50 as YES and rest of NO.

For ex:

Flag field should have YES, NO values. If I select flag field from dimension list and Revenue from expression list Report look like

ID,    Flag, Revenue, desc order

D111, YES, 10200, 100

C543, YES, 10100, 99

-

U211, YES, 5000, 50

V001, NO, 4900, 49

- 

A011, NO, 1, 1

Note : Desc Order filed has been provided for understanding.

Regards,

Nihhal.

[agustinbobba]

Hi Nihhal,

Maybe you can do it in the script

Load
     ID,
     Revenue,
     IF(Revenue > 50 , 'Yes', 'No') AS Flag
From XXX;

Best regard

Agustin.

[nihhalmca]

Thanks for reply.

Just i have given example as revenue 50. I am expecting top 50 values in revenue filed. (50 is count, should not be revenue value)

[amit_saini]

Try:

I guess you can use Rank function for top 50 and than later use if statement based on Rank (Revenue) for Yes and No.

Thanks,

AS

[nihhalmca]

Thanks for response.

For all i know QV does not have any rank funcion for edit script (script level), can you share sample script please.

[nihhalmca · Accepted solution]

I got the solution by using of rec().

[Anonymous]

Table:

LOAD * Inline [

NAME, VALUE

A, 4.4

B, 0.9

C, 3.2

D, 1.92

];

Table1:

LOAD *,

  If(Rank <= 50, 'Y', 'N') as flag;

LOAD NAME,

  VALUE,

  AutoNumber(VALUE) as Rank

Resident Table

Order By VALUE desc;

DROP Table Table;