Working out highest grouping

[#1]

Hi

I have a table, SalesCategory which works out a field called TOP_CATEGORY based on the number of sales. What I instead want is to populate TOP_CATEGORY based on the 'amount' of sales ie vSalesAmount :

vSalesPerson

vSalesAmount

SalesCategory:

NOCONCATENATE LOAD

$(vvSalesPerson)

Mode(LEVEL1) as TOP_CATEGORY

RESIDENT REPORT_STAGING

GROUP BY $(vSalesPerson);

[swuehl]

Please post some sample lines of data and your expected resulting table.

[sunny_talwar]

I agree with Stefan, not sure what you are trying to do. It would be easier to help if we have a sample to work with

[#4]

Certainly. Here's a spreadsheet with the original sample data in the first tab and expected outcome in second tab.

As you can see currently it pulls of the 'top_category' based on the highest number in column C. Instead I want it to pull the corresponding top_category based off the highest sales amount in column B.

Please let me know if this is not clear as I can appreciate it's the clearest example.

[settu_periasamy]

Hi,

You can try with 'FirstSortedValue', like

LOAD  FirstSortedValue([Level 1],-vSalesAmount) as TOP_CATEGORY

[sunny_talwar]

Continuing Sethu's logic further to front end....

If you are doing this in a text box objects, you can try this:

FirstSortedValue([Level 1], -vSalesAmount)

or if you want this in a chart where Level 1 is a dimension:

FirstSortedValue(TOTAL [Level 1], -vSalesAmount)

[#7]

Thanks both. However this gives the first value which returns the corresponding category of

Management and Business Professionals and Administrative Services

However I want it to return the category relating to to the 'first highest value' so I am expecting to see returned :

Apparel and Luggage and Personal Care Products

[swuehl]

It would be easier to help with a sample of your records in the data model, not showing pre-aggregated data.

I assume you have sales data per person and category, and you want to show in a table the overall top category across all sales persons. Maybe something like this

=FirstSortedValue(DISTINCT Level1, -Aggr(Sum({1} SalesAmount), Level1) )

Or in the LOAD script:

SalesPerLevel1:

LOAD Level1,

          Sum(SalesAmount) As Level1Sales

FROM YourSalesTable

GROUP BY Level1;

SalesPerPerson:

LOAD Person,

          Level1,

          Sum(SalesAmount) As PersonSales

FROM YourSalesTable

GROUP BY Person, Level1;

JOIN (SalesPerPerson)

LOAD FirstSortedValue( Level1, -Level1Sales) as TopSalesLevel1,

RESIDENT SalesPerLevel1;

[#9]

Ignore that ! Think I have it working now ...

Will update you  later once validated.

Many thanks