Hey Sonali Can you please upload a sample package for better understanding
Interesting challenge. I think you can do this using the Index function.
Create a calculated field called INDEX with the formula INDEX(). That will give each bar a value, starting at 1 and out to 96 when you're at month level.
What it sounds like you want is to return entries 94-96 at month level, 28-32 at qtr level, 12-16 at half year and 4-8 at year level - i.e. the 5 latest values based on the selection.
Therefore we need to calculate the latest value. Create a new calculated field called LatestEntry with the formula WINDOW_MAX([INDEX]) to return the number of the latest entry.
To finish off we need to create a calculated field to act as our filter. This would have the formula [INDEX] >= [LatestEntry] - 4. Drag that to the filters shelf and select True.
Attached a 9.2 workbook showing it with the superstore dataset.
Last5Demo.twbx 1.2 MB
Thanks for answer. Its really helpful.
But I have one more problem. I have one year parameter, I want to show last 5 qtr or month from current selection of year.
1. Initially when am looking at my dashboard it should show me all year is my data.
2. For Say, I am selecting year 2014-2015 it will give last 5 year (14-15,13-14,12-13 ,etc)
3. Now I am drilling down at quarter level having selected year 2014-2015, then it should give me
4 quarter from 14-15 and last quarter from 13-14
4. Same when I am at month level it should give me 5 months of current selected year.
Index function works properly without my year parameter. Its also giving me correct data what I want.
But year selection is also one of the requirement.
Client wants to see all year at first level and then drilling down to other.
Problem is when I am using parameter for year selection, my index is not working properly.
and it wont give correct answer.
Sry I cant share workbook, I was using trial version of tableau. and its expired now.