6 Replies Latest reply on Feb 11, 2016 8:08 AM by JayC

# Top N based on % change - Help please

Hi All,

Background:

I have 2 dimensions say "send_city" & "recv_city"

Have measures like "salesold", salesnew", "profitold", "profitnew" and so on...

Reqmt:

I want to have % difference calculated between old and new fields with parameter selection having values like sales, profit, shipping cost etc.,

Ideally objective is to find out "Top N"

"send_city" TO "recv_city"

where % diff between say salesold and salesnew fields  >= 10%

On top parameter should keep switching sales/profit/shipping cost etc., based on selection

• ###### 1. Re: Top N based on % change - Help please

Jayachandra,

Here are some instructions on creating sets of the top customers, then utilizing this in a parameter with quick table calculations to find the percent difference.

1. Right-click on [Customer Name] in the data pane and select Create > Set…

2. In the Create Set dialog, do the following and click OK:

+ Name the set. In this example I will call it "Top 25 Customers"

+ Navigate to the Top tab

+ Select By field:

+ Enter 25 for the number of top members

+ Select Sales from the dropdown menu for fields

3. Create a calculated field with a name like "Percent of total for all top 25" with a calculation similar to the following:

WINDOW_SUM( SUM(

IF [Top 25 Customers]

THEN [Sales]

END

)) /

TOTAL( SUM( [Sales] ))

*Attached is sample workbook that explains the above solution in the caption. I would utilize this to create sets for each of categories that you would like to analyze.

• ###### 2. Re: Top N based on % change - Help please

Hi Patrick,

Actually I am almost near the end of my requirement BUT am unable to sort as desired.

Any chance looking into my attached file please and suggest where am going wrong or how to fix sort issue!

Thanks again!

• ###### 3. Re: Top N based on % change - Help please

Jayachandra,

What is the expected sort behavior that you are looking for? My assumption is that you would like to sort based on the metric. And currently it is sorting by state. Is this a correct assumption?

-Patrick Byrne

• ###### 4. Re: Top N based on % change - Help please

Yes i want the sort to happen on metric

• ###### 5. Re: Top N based on % change - Help please

Hi Patrick,

Have used nested sort and got sort thing to work. please see attached.

But still there is 1 issue i am facing-

In the below screenshot #61k is not the top bar, its currently sorting within each group of state/city. Basically its not ranking sales i believe. Any insights please