-
2. Re: Sum of [measure] on the basis of {Top N rank} with (date as filter)
Devesh Soni Aug 3, 2018 6:14 AM (in response to Naveen B)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)
Naveen B Aug 3, 2018 7:01 AM (in response to Devesh Soni)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)
Ankit Bansal Aug 5, 2018 10:51 AM (in response to Devesh Soni)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)
Devesh Soni Aug 5, 2018 11:22 PM (in response to Naveen B)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)
Devesh Soni Aug 5, 2018 11:23 PM (in response to Ankit Bansal)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)
Naveen B Aug 5, 2018 11:38 PM (in response to Devesh Soni)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)
Ankit Bansal Aug 7, 2018 12:21 AM (in response to Devesh Soni)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.
-
TopN_v10.5.twbx 8.1 KB
-