topic Re: Working out highest grouping in QlikView

Working out highest grouping

Fri, 17 Jun 2016 15:16:16 GMT

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);

Re: Working out highest grouping

swuehl — Fri, 17 Jun 2016 15:19:22 GMT

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

Re: Working out highest grouping

sunny_talwar — Fri, 17 Jun 2016 16:13:03 GMT

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

Re: Working out highest grouping

Mon, 20 Jun 2016 00:02:23 GMT

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.

Re: Working out highest grouping

settu_periasamy — Mon, 20 Jun 2016 02:58:12 GMT

Hi,

You can try with 'FirstSortedValue', like

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

Re: Working out highest grouping

sunny_talwar — Mon, 20 Jun 2016 03:28:12 GMT

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)

Re: Working out highest grouping

Mon, 20 Jun 2016 10:15:37 GMT

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

Re: Working out highest grouping

swuehl — Mon, 20 Jun 2016 10:30:11 GMT

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;

Re: Working out highest grouping

Mon, 20 Jun 2016 10:30:45 GMT

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

Will update you later once validated.

Many thanks