I want to filter out data from a bumpchart when data for the last year is missing.
In the above chart, DVB, should not appear.
It would also be nice to have the Year axis at the top.
Workbook is attached.
Jaya,
Apologies, I didn't read carefully.
Please see if this is closer to the goal.
It can be done with table calculations:
[Max Date Region]=[Max Date All]
Where both are the same:
WINDOW_MAX(MAX(YEAR([Survey Date])))
just have different table calculation settings:
Max Date All is the max year in the whole data set.
It is across all dimensions, without restarting.
Whereas Max Date Region restarts every region.
I think you will still need to use the
LOOKUP([MaxFilter],0)
method to maintain the bump positions.
Sorry for not reading fully.
Jaya,
Also here is a better method for moving the column headers to the top:
Copied the calculated fields you created and used as suggested, but not able to replicate.
On the surface, this should be simple - if there is no data for 2018, filter out, but why is it so difficult! You seem to have it working, but I'm not able to.
Jaya,
Apologies for not clarifying.
I suspect the difference may be in the details of the settings
of the table calculations, particulary the nested calculations.
In the attached workbook in the Forum Thread, I have made a few changes.
I created a Survey Date Year (using Custom Dates )
and a parallel Survey Date Label Year (sorry it doesn't look like
the other method for moving axis to the top works here).
This allowed me to use these dimensions in the settings for
the Table calculations.
So in this new workbook I have edited the Max calculated fields
to have a default table calculation setting. I think these should transport
over when copying the pill to a different workbook, (as long as
the new Custom Year fields described above are in place).
Regardless, in the below image I show what settings I was using
for each nested calculation.
Apologies also if you have already set them this way and it isn't working.
Am happy to help troubleshoot.
I am sorry, I don't understand how your solution works.
My apologies for injecting the request for moving the axis to the top; it is not only a distraction, but has injected further complexity into the solution.
The good folks at Tableau Tech Support provided a solution that is straightforward and works great:
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
To filter out the Regions with no data in the max date we will need to take the following steps:
{ FIXED : MAX(YEAR([Survey Date])) }
{ FIXED [Region]: MAX(YEAR([Survey Date])) } = [!MAX Date]
LOOKUP(MIN([!Region Has Max Date]), 0)
The reason we need this extra calculation is because the view uses Table Calculations and therefore we need to filter the view without filtering the underlying data. If we want the table calculations in the view to be affected by the filter then we can simply drag the calculation from Step 2 onto the filter and select True.
Please see the two different sheets for the different results of applying step 2 as a filter or step 3 as a filter. The filtered rank is using step 2.
Due to the FIXED expressions we are using to calculate the years there may be issues with filters since they are ignored by FIXED. If this is the case and we want to see the filters affect the view, we can apply them to Context by right clicking it and select Context.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
I moved the x-axis header to the top by using dual-axis and editing out the bottom axis.
Thanks.