9 Replies Latest reply on Aug 7, 2018 12:21 AM by Ankit Bansal

Sum of [measure] on the basis of {Top N rank} with (date as filter)

Hello there,

I am creating a calculation in which I would like to calculate SUM([Quantity]) on the basis on Top N rank of Product Name.

I have tried a calculation on Rank and using that in filter but that hardly worked.

Calculation used in filter -

IF RANK(SUM([Quantity]))<11 THEN

RANK(SUM([Quantity]),'desc')

END

I want to show only 1,005 on sheet. (Product name wise SUM of Quantity on Rank<=10)

Although I got this:

Data Source - Sample Super Store

Version - Tableau Desktop 10.5

Any help would be appreciated.

Thank You.

• 1. Re: Sum of [measure] on the basis of {Top N rank} with (date as filter)

Hi Devesh,

Check the below image

add the date field to context and apply top N on Product name based on the date filter automatically your total value will change

BR,

NB

• 2. Re: Sum of [measure] on the basis of {Top N rank} with (date as filter)

Hello Naveen,

Thank you so much for replying in such a short time.

Although the solution which you suggested I did tried that but that didn't work.

I want to use RANK() function only; because if for 2 different product the sum of quantity is same then Tableau gives same rank to both and if I am to look for products whose rank are less-than-equal to 15 and two or more products have same RANK then list of products is different.

I am attaching below the snapshot just to convey what I am trying to achieve.

1. When calculated using Top 15, then grand total is:

2. When using RANK function the sum is 1385

I am only interested in 1,385 as I have to display this as KPI in dashboard.

Thank You.

• 4. Re: Sum of [measure] on the basis of {Top N rank} with (date as filter)

Hi Devesh,

Follow the below steps to solve the problem

1) Create a calculated field like below

2) Click on Product name --> Create --> Set --> Top --> Select the created field (product name sales) --> 10 ok

3) Drag the set to filter

4) drag the created calculated field to text you will get the top 10 value

Original:

KPI Value:

Hope this helps kindly mark this answer as correct and helpful so that it will help others

BR,

NB

• 5. Re: Sum of [measure] on the basis of {Top N rank} with (date as filter)

Devesh,

I see there are 2 problems here:

1- First is set does not compute RANK(). Means it won't give same rank to 2 two products if they have same sales.

2- RANK() is a table calculation. So if you don't keep the dimension on which you are trying to calculate rank, you can not calculate rank and so you can not use it as filter.

So now one option is you keep the product_name also in your view and select show totals . On the dashboard use Floating Dashboard and adjust it so that only grant total is seen

I will get back to you if I find any better way of doing it.

Thanks,

Ankit Bansal

• 6. Re: Sum of [measure] on the basis of {Top N rank} with (date as filter)

Hey Naveen,

I tried this method, although it didn't gave me result which I expected.

Thank you.

• 7. Re: Sum of [measure] on the basis of {Top N rank} with (date as filter)

It would be great if i get a clean solution.

Thanks Ankit !

• 8. Re: Sum of [measure] on the basis of {Top N rank} with (date as filter)

Hi Devesh,

May i know what going wrong?

BR,

NB

• 9. Re: Sum of [measure] on the basis of {Top N rank} with (date as filter)

Hey Devesh,

Got a work around for this. In below example let say [ID] is product_name and [mock] is sales.

First create a set of top N on [ID]: TOP_N Set :

You can pass parameter instead of 2.

Then create a calculated field as: top N Sales

Then create a calculation filter as : T|F

Then put this on filter shelf and choose TRUE. Drag the sales on shelf and you are there

Attaching the workbook for reference.