Finding Max Date

[#1]

I have a pivot table with diminsions of center and date. The user would like to see an as of inventory count by center so that when both the center and date are expanded, the inventory shows as of each date, and when the date dimension is collapsed, the inventory for the last date selected is displayed for the center. Additonally, in the total for all centers they would like to see the total inventory as of the last day.

For example, if we had the following inventory:

As Of Date	Center	Units
1/1/2010	A	3
1/1/2010	B	4
1/2/2010	A	3
1/2/2010	B	5
1/3/2010	A	2
1/3/2010	B	5

If the user selected a date range of 2010-01-01 through 2010-01-03, with all dimensions expanded the user wants to see this:

Center	Date	As Of Inventory
A	1/1/2010	3
A	1/2/2010	3
A	1/3/2010	2
	Total	2
B	1/1/2010	4
B	1/2/2010	5
B	1/3/2010	5
	Total	5
Total		7

And with the Date dimension collapsed they want to see this:

Center	As Of Inventory
A	2
B	5
Total	7

The AsOfDate is linked to a separate field called Date, created by a generated calendar island, and this is the field used for the date selection. I am trying to use the max() formula in set analysis but I am having no luck, only null is returned. I haven't had any luck with rangemax(), firstsortedvalue() or getselectedvalues(). Here is what I think should work, but it is not. Any suggestions?

Sum({$<AsOfDate = {"$(=max(Date))"}>} Units)

[johnw]

This probably isn't it, but one very simple gotcha is that QlikView is basically doing a text comparison of your dates instead of a numeric comparison, so the formats have to be the same. It's probably not it because most people use consistent date formats across the entire application, and these formats match the default from the script. Another thing that shouldn't make any difference is that double quotes specify a search string, not a speficic value. It doesn't make any difference, because searching for a specific value gives the same result as just specifying that value. Still, addressing those potential issues, my first try would be this:

sum({<AsOfDate={'$(=date(max(Date),'M/D/YYYY'))'}>} Units)

But it's probably something more fundamental. Also, that's your formula for the totals, not for the underlying data, and I'm not sure how you're handling the transition.