How to write an firstsortedvalue expression

[alexpanjhc]

Hi

I want to show data like following

Category, Sub Category, Revenue , RANk

A,     ABC,  1000,1

A,     AAA, 100

A,     ACB, 200

B, BBB,500,2

B,ABB, 100

so for the rank, I only want to show rank if within the category, the subcategory has the highest revenue, i will show the rank. I thought firstsortedvalue will do the trick. I simplified the table as the revenue is an aggregation. how do I achieve above table?

firstsortedvalue(aggr(Sum(revenue), catgeory, subcategory), subcategoty)? i do not think this looks correct though.

please advise.

thanks!

[Anil_Babu_Samineni]

Correcting from your expression, try like

firstsortedvalue(subcategoty, aggr(Sum(revenue), catgeory, subcategory))

[alexpanjhc]

Thanks for reply @Anil_Babu_Samineni 

it is only showing the subcategory, but i only want to show where the revenue has the biggest number. 

Also if catgeory is not showing in the chart, can this be shown the same way?

thanks!

[Anil_Babu_Samineni]

For #1, You can try this

firstsortedvalue(subcategoty, aggr(Max(revenue), catgeory, subcategory))

For #2, I didn't follow, can you please explain more.

Anyway, It's good to demonstrate if you can share the QVF to test with. 

[alexpanjhc]

@Anil_Babu_Samineni 

I am not able to upload the file but i used inline to load and I am using a table to show using Category, sub catgeory, revenue and your formula, each line shows different sub category.

A:
load * inline [
Category, Sub Category, Revenue

A, ABC, 1000

A, AAA, 100

A, ACB, 200

B, BBB,500

B, ABB, 100

];


B:
load * inline [
Category, Rank
A,1
B,2];

[Padma123]

A:
load * inline [
Category, Sub Category, Revenue

A, ABC, 1000

A, AAA, 100

A, ACB, 200

B, BBB,500

B, ABB, 100

];
NoConcatenate

table2:
load Category, [Sub Category], Revenue ,If(rank=1,rank,0) as rank;
load * ,AutoNumber(Revenue,Category) as rank Resident A
order by Revenue desc
;

drop Table A;