Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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....
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.
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.