neither firstsortedvalue nor max(testdate) getting desired result

[stevelord]

Hi I've tried these two formulas on a pivot table and get 'Expression Ok' from Qlikview, but my testdate column is picking the february test when it has a choice between february and march 2012 tests.  I have a separate tablebox with userid, testdate, and testvalues with all dates/values showing to compare my pivot table to.  Strangely, my pivot table doesn't seem to change whether I have the - or not in firstsortedvalue, and whether I have max(TestDate) or min(TestDate)  Any idea what could cause this?  Whatever the data source, it seems to me that toggling the - sign or toggling min/max on TestDate should make the table change.. (Yes, I pushed apply and okay. )

Dimensions: ClientName(left out of below view), UserId (ficticious), TestDate

Expressions tried all produced the tables shown below::

if([rbTotal Cholesterol],Count(aggr(firstsortedvalue([rbTotal Cholesterol],-1*TestDate), UserId)))

if([rbTotal Cholesterol],Count(aggr(firstsortedvalue([rbTotal Cholesterol],TestDate), UserId)))

if([rbTotal Cholesterol],Count(aggr(max(TestDate), UserId)))

if([rbTotal Cholesterol],Count(aggr(min(TestDate), UserId)))

The if might not be needed on firstsortedvalue- it just sort of carried over from when I tried max(TestDate)

PivotTable
UserId	TestDate	[rbTotal Cholesterol]	Optimal	Borderline	Out of Range
702	2/14/2012			1
708	2/14/2012				1
711	2/14/2012				1
716	2/14/2012				1
718	2/14/2012		1
719	2/14/2012				1
720	2/14/2012				1

Tablebox

UserId	TestDate	[rbTotal Cholesterol]
702	2/14/2012	Borderline
702	2/16/2012	Out of Range
702	3/15/2012	Out of Range
708	2/14/2012	Out of Range
708	3/15/2012	Borderline
711	2/14/2012	Out of Range
711	3/15/2012	Optimal
716	2/14/2012	Out of Range
716	3/15/2012	Optimal
718	2/14/2012	Optimal
718	3/5/2012	Optimal
718	3/15/2012	Optimal
719	2/14/2012	Out of Range
719	3/15/2012	Out of Range
720	2/14/2012	Out of Range
720	3/15/2012	Optimal

Note: Total Cholesterol field was created by a SUM(if(TestName='Total Cholesterol',TestValue)) as [Total Cholesterol] in a table grouped by Client, UserId, Date(Floor(TestDate)), and Year(TestDate).  The [rbTotal Cholesterol] was part of a subsequent resident load for if([Total Cholesterol]<200, dual('Optimal','Optimal'),.... etc as [rbTotal Cholesterol]  The original qvd file had columns UserId, TestName, TestValue, TestDate.  I worried about dates losing their links to the tests, but the tablebox looks okay in that regard and I only have one TestDate field available....

[stevelord · Accepted solution]

Alright! I figured it out while complaining to my boss over IM. My aggr statement was just missing a couple of related fields needed for its grouping/group by framework.  Table didn't change until the last field of the group by was snapped into place.

So it was blahblahblah, Count(Aggr(max/firstsorted(whateverthing), UserId, Source, TestYear)

So I was just short on listing fields to group it by for the aggr function.  And if you have a table that doesnt change when you switch between min/max or +/-, you can check and add things to your aggr fields and save alot of money by switching to gecko.

PS> I spent another half hour experimenting to confirm this.  Table goes back to ignoring me when I take one of the underlined aggr fields back out and back to correct when I put the fields back in.