I have a Straight Table chart which shows injury data. It is used in a report showing injury data from several years. I need to restrict the Straight Table to show only the most recent Fiscal Year of data. I have a field [FiscalYear] which has the data "2008/2009", "2007/2008", "2006/2007", and so on. The user can select the years they want to show in the report and I need the Straight Table to only show the most recent year selected. I searched the fourms and just can't find anything that helped make this work.
I have tried using the following
a calculated dimension: =maxstring(FiscalYear)
a calculated dimension: =if(FiscalYear=maxstring(FiscalYear), FiscalYear, null())
a dimension: =maxstring(FiscalYear)
a dimension: =if(FiscalYear=maxstring(FiscalYear), FiscalYear, null())
None of these work. I just get all the data from all the Fiscal Years. I have no idea how to do this but know it can be done as I was provided some conde to use as an expression in limiting data in my pie graphs to the most recent FiscalYear selected.
One example of the expression which is working is: count({<FiscalYear={'$(maxstring(FiscalYear))'}>} ClaimCause)
Thanks for any help or advice that you can provide.
I tried that as both a dimension and as an expression and neither worked. I just got a null value in the new column for all rows and no filtering to the max string value. I clicked "supress when value is Null" and all the rows of data disappeared. When I added a "1" to the expression for a non-match and all the rows returned with a "1" in that column.
