2 Replies Latest reply on Feb 1, 2016 8:33 PM by Tableau kumar

Using Rank in a filter with partition

Hi,

Suppose I had a table that had two dimensions - category and date, and one measure - sales and I wanted to create a calculated field that ranks by date partitioning by category so I could, for example, look at average sales by date only for categories in that particular date that have been around for n periods.  Stated more simply, is there a way to create a calculated field that would rank each category by date so then I could simply filter on that field?  Thanks!

• 1. Re: Using Rank in a filter with partition

Hi Tony,

I may be wrong here, but it sounds like you'd like to create a view that only shows categories that have sales data for both a user selected data and the previous N date periods. For example, the user might choose April 2015, and the view would only show categories that have date for all month between Feb - April 2015. Is that correct?

We can create a filter using level of detail (LOD) expressions that will evaluate if a category has data for all desired months.

1. Create a calculated field with a name like "Month of Date" with a calculation similar to the following:

DATETRUNC( 'month', [Date] )

2. Right-click [Month of Date] in the data pane and select Create > Parameter...
3. In the Create Parameter dialog, Tableau Desktop has automatically created a parameter with a list of months. Click OK to create the parameter
4. Create a calculated field with a name like "Has sales for desired months" with a calculation similar to the following:

IF [Month of Date] >= DATEADD( 'month', -2, DATETRUNC( 'month', [Date Parameter] ))
AND { FIXED [Month of Date] : SUM( [Sales] ) } > 0
THEN 1
END

In the above calculation, the first conditional statement evaluates whether a month is within the desired time span (in this case the user selected month or two months prior). The second conditional statement evaluates whether the sum of sales for that month is greater than zero. In this case we are fixing the sum of sales per month, so that the calculation can be used as a filter to create the view.

5. Create a calculated field with a name like "Has Sales for selected month and 2 previous months Filter" with a calculation similar to the following:

{ FIXED [Category] : SUM( [Has sales for desired months] )} = 3

The above calculation evaluates if a category has 3 months of sales data that fall within the desired time span. If so, the calculation will return TRUE.
6. Drag [Has Sales for selected month and 2 previous months Filter] to the Filters shelf
7. In the Filter dialog check True and click OK

• 2. Re: Using Rank in a filter with partition

Hi,

I am not sure whether the foloowing link helps you, but would like to share

Best Regards

Kumar