topic Re: FIlter data in Straight Table in QlikView

FIlter data in Straight Table

Thu, 08 Jul 2010 20:18:01 GMT

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.

FIlter data in Straight Table

johnw — Thu, 08 Jul 2010 20:42:17 GMT

Maybe this?

=if(FiscalYear=$(=maxstring(FiscalYear)),FiscalYear)

FIlter data in Straight Table

Thu, 08 Jul 2010 20:58:54 GMT

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.

Any other suggestions?

Thanks for helping again.

FIlter data in Straight Table

johnw — Thu, 08 Jul 2010 21:23:02 GMT

Syntax error. I was missing single quotes around the string value:

=if(FiscalYear='$(=maxstring(FiscalYear))',FiscalYear)

See attached working example.

FIlter data in Straight Table

Thu, 08 Jul 2010 21:33:28 GMT

Awesome. Again, your coding works great. Thanks again.

Re: FIlter data in Straight Table

Tue, 05 Feb 2013 07:31:19 GMT

hi john,

i'm using qlkview personal edition.so, i'm unable to open your qvw doc.

could you plz make ur docs to be opened in qllikview personal edition.