How do I show the count for a varaible value within Text in Chart?

[#1]

Hey all, first post here! I looked around in the search a bit and couldn't find a similar problem so I thought I'd ask here.

Currently I have a chart with CreateDate (a date field) on the X-axis and Count(AssetID) on the Y-axis. I want to add a text box that displays the Count(AssetID) value for the most recent date on the chart. If I use something like '=count( {$<CreateDate = {"2011-06-01"}>} AssetId)' it returns the correct number. However if I try something like '=count( {$<CreateDate = {$(#MaxDate)}>} AssetId)' where the MaxDate is a varaible that equals MaxString(CreateDate) it returns 0. What am I doing wrong to try and get this to work for a dynamic value?

Thanks!

[prieper]

The problem is the mixing of formatted dates and numbers, as MAX delivers only a numeric value. So it makes sense to have your "CreateDate" as numeric values as well.

HTH
Peter

[#3]

Are you sure? For me the expression '=MaxDate' and '=MaxString(CreateDate)' both return the value '2011-06-01'. It's only if I try to do something like '=MaxString(CreateDate)+1' does it return a numeric value (days since 1900 I believe).

[johnw · Accepted solution]

The first thing I'd try is $(MaxDate) instead of $(#MaxDate).  I believe $(#...) specifically tells QlikView to return the value as a number.  If that doesn't work, you can always try $(=date(MaxDate,'...')).  If that works and $(MaxDate) doesn't, then that at least suggests that it's a formatting problem, since all date() does is format.

I'm also unclear from your explanation if MaxDate itself has single quotes, or if you were just putting the value in single quotes as a way of saying that's what the value is.  If it doesn't return the single quotes, you'll probably need to put it in single quotes in the set, =count({<CreateDate={'$(MaxDate)'}>} AssetId).

[#5]

Thanks for the info! I tried with the single quotes and it worked! For reference this was the final expression:

=count( {$<CreateDate = {'$(MaxDate)'}>} AssetId)

Thanks again, you rock.