CODEWITHSUNDEEP
filtertableau-apirank
stack_pointer is EXTINCT
stack_pointer is EXTINCT

Reputation: 2393

Rank only for latest month

I've a calculated field "Total Revenue" which blends revenue from multiple data sources in Tableau. Based on this field (aggregate field) on monthly basis, I would like to show only TOP 5 items with highest revenue as of latest month.

table screenshot

As shown in above table, items have to be filtered out (as they are TOP 5 in March) based on revenue data as of the latest month (March). How can this be achieved using RANK()? I'm not able to rank only for latest month as formulated below because it shows error as I cannot mix aggregate and non-aggregate functions. 

IF DATETRUNC('month'),ReportDate)=//March 1st date given// 
THEN RANK(Total Revenue)
END

Upvotes: 0

Views: 1133

Answers (1)

Hakan ERDOGAN
Hakan ERDOGAN

Reputation: 1210

My solution is a kind of workaround but seems to be working:

  • Create a calculated field with below formula:

IF DATEPART('month', {MAX([date])}) = DATEPART('month', [date]) and DATEPART('year', {MAX([date])}) = DATEPART('year', [date]) THEN [revenue] ELSE 0 END

The {MAX([date])} part on the code gets the maximum date in your data, it is fixed with { and } characters so that the value is not effected by the filters, date partitions etc. If you want the sorting month to be the one we are in then you should change that part with NOW()

Now we have a value containing only the sum of the latest month and we can sort our visual with this Measure.

You can drag your new measure (I called my measure: 'last month revenue') to the details and right click your item pill on the Rows, sort it by your new field.

And finally drag your item to the filters and go to Top tab, make filter your data according to sum of "last month revenue" measure.

Below screenshot shows the excel data and the final Tableau table:

enter image description here

Upvotes: 1

Related Questions