7 Replies Latest reply on Oct 15, 2016 4:09 AM by Rohit Kumar

How to show % of total on Top N parameter

Hi all,

I am facing a issue when i filtering "category" wise "customer name" sales  using "Top N parameter" , i added Rank function, through Rank function i used one calculated filter to filter Parameter value. Till this i am very clear and i am getting correct values but i want category  wise % of total aggregation. when using parameter either product filter i want total of % is 100.

If i use index, it's filtering over data n showing top customer name wise top values only (ex: if i filter Top 5, it's  taking 2 values from one category and 3 values from  another category) , but i want every category wise customer name  sales only. here working % of total is fine.

Actual requirement  is  "Category wise Top N  sales with % of total should be 100% after filtering the data".

See my requirement in attached worksheet.

• 1. Re: How to show % of total on Top N parameter

Hi Praveen,

Check out the attached twbx.  In this example I use WIndowSum, WINDOW_SUM(IF [Sales Rank] <= [top] THEN SUM([Sales]) END), to create the denominator.  Take a look and let me know if you have any questions.

Regards,

Ivan

2 of 2 people found this helpful
• 2. Re: How to show % of total on Top N parameter

Hi Ivan,

Great!! Working well. Thank you so much for the help.

Just for my own knowledge, What is the difference b/w RANK & RANK_UNIQUE?

What does the WINDOW_SUM statement do in the logic?

Thanks,

Praveen

1 of 1 people found this helpful
• 3. Re: How to show % of total on Top N parameter

Hi Praveen,

The difference between RANK and Rank_UNIQUE is that RANK_UNIQUE will return a unique rank number  even when you have a tie in ranking,  whereas  with RANK you could have two customers ranked #1 if they are tied.  See below.  Either should work fine for you, I just have a habit of using rank_unique.

WINDOW_SUM will return the SUM of all records in the partition, which according to the compute settings would be Category.  Since WINDOW_SUM is a table calc it would normally return the SUM of all sales per category, however using IF [Sales Rank] <= [top] THEN SUM([Sales]) instead of the total sales for the Category.

Regards,
Ivan

 Name Sales Rank Rank_Unique Bob 1000 1 1 Joe 500 2 2 Jane 500 2 3 Dan 400 4 4 Dave 300 5 5

2 of 2 people found this helpful
• 4. Re: How to show % of total on Top N parameter

Hi Ivan,

Really thanks for your help and good explanation.

So, we can use anywhere RANK_UNIQUE in place of RANK function ?

Thanks,

Praveen

• 5. Re: How to show % of total on Top N parameter

i think you should apply the table calculation on sales take the calculation in percent of total and addressing as Pane down then it gives the

• 6. Re: How to show % of total on Top N parameter

Hi Praveen,

I wouldn't say you could use Rank_unique in place of Rank, they are different and it would depend on your use case.  For this view I think either should work fine.  Rank_unique is like Row_Number() in SQL.  Below is a link that explains the difference between Row_number, Rank and Rank_Dense.

The Difference Between ROW_NUMBER(), RANK(), and DENSE_RANK() – Java, SQL and jOOQ.

Regards,
Ivan

2 of 2 people found this helpful
• 7. Re: How to show % of total on Top N parameter

Hi Ivan,

Thank you.