5 Replies Latest reply on May 21, 2018 8:59 AM by Andrew Watson

    Calculating Years in View

    Daniel Woolcott

      Hi There,


      Link to workbook on Tableau Public is included at the bottom of the post.


      I'm having an issue with the role of the range of dates in a workbook I'm preparing.


      I've been playing with tableau on the public version to check its applicability to the business I work for before we take the plunge. All is well, other than one very specific issue I've had with arranging data which I can't seem to fix.


      We use a rate which is calculated as follows : Total Cost / (Number of Beds * Number of Years). This gives the cost per bed per year. Cost & beds are fine - a sum value and an attribute respectively. The issue is the number of years.


      I would like the number of years to be adaptive to the range selected by the user .e.g if they highlight one year only, they see the rate over that timeframe. Or if they select 3 years, that the rate is for 3 years. If they filter by category, that the number of years stays the same.


      However I'm having issues here: all years have a record present, but not all categories appear in each year. So as soon as a category filter is applied, I'm having issues preserving the original year count. However I would like to be able to change the number of years by action filter or slide filter, so that it is not just a fixed value.


      I've tried COUNTD and MAX functions, but both have issues - not all categories have spend in every year - so an application of a category filter can reduce the number of years by COUNTD.


      Any suggestions for how to calculate the number of years present in the view, rather than based on existing records would be most welcome.


      Link to public workbook:

      Tableau Public