Expression to get Pivot table to show most current test value of the year in Year field

[stevelord]

Hi, I have an issue with Pivot table's TestYear field giving me a null value if someone had more than one TestValue in that year.

My expression is this:

If(BMI, Count((Aggr(Max(TestDate), ClientAccountName, UserId, TestYear))))*BMI

I use a similar if/aggr(Max(TestDate)...) formula on pie charts to single out the most current testdate and count values and such.  For the pivot table I figured I could get it to look at the most current TestDate with the same formula, then multiple the count (would be 1 or 0) by the actual testvalue to have it put the testvalue in the field.

The expression does a nice job populating correct testvalues where only one value is present for the year, but it gives nulls if two values are present for the year.

Now as a fun kicker, if I add TestMonth to the aggr formula and TestMonth field to the pivot table, the pivot table gives me the value for the most current TestMonth in the year, but a null for any values in previous months- which is what my formula wants I suppose.  When I take TestMonth back out of the expression and fields, it throws that single value back out and gives me a null again- which makes me come here and post.

I don't think I want firstsortedvalue() function because it might not necessarily point to the most current testdate in the year.  max(TestDate) should point to the single desired testvalue, but I'm missing something.

[Gysbert_Wassenaar]

Can you post an example document? I think you'll need to aggregate BMI too in some way, but that's just guessing without being able to look at the real thing.

[stevelord]

Attached is the text of the script leading up to the creation of the BMI field.

I used sum(if()) and group by to create it from TestName and TestValue fields in our raw biometrics table, then did a resident load to populate null values in the new BMI field if we had height and weight values from which to compute BMI for those.

Here is what I have in the pivot table object itself:

Dimensions: ClientAccountName, UserId, TestYear

Expression: If(BMI, Count(Aggr(Max(TestDate), ClientAccountName, UserId, TestYear)))*BMI

Then the pivot table layout is:

ClientAccountName     UserId     Year*     2013     2012     2011     2010

A                                   123                    24.0     23.8     -          -

A                                   456                    -          25.0     22.0     -

A                                   789                    24.9     24.8     24.3     24.0

Most of the nulls are fine, but some are the result of two testvalues in the year and the pivot table giving a null rather than pick the most current value.  I have been able to confirm this by looking at the data in tableboxes

ClientAccountName     UserId     TestDate     BMI

A                                   123       8/1/2013     24.0

A                                   123       7/6/2012     23.8

A                                   123       8/17/2011     23.6

A                                   123       4/15/2011     23.7

etc.

I couldn't give the actual qvw because it has sensitive information, but hopefully the script formulas I wrote and object setup information is all you need to help me.

[stevelord]

In the script I did see one thing to fix about the testdate, but it only resolved an issue with items on tableboxes being summed incorrectly and not the issue of max(testdate) not getting the most current of the two values.

It was originally a Date(Floor(TestDate)) and I switched it to a TimeStamp(TestDate) as TestDate,

And I replaced the date(floor(Testdate) in the group by with the TimeStamp(TestDate)

[stevelord]

As another piece of information, I have one case where it shows the value if a person has multiple testdates with identical BMIs in that year, but still doesn’t show the value if the person has multiple testdates with different BMIs. I’m sure you’re right about needing to aggregate the bmi directly somehow, but not sure the precise syntax… still experimenting.

[stevelord]

As a poor workaround, sticking the TestDate field into the pivot table on the Y axis and leaving testyear on the x axis brings out the values that the pivot table tries to null otherwise. But then it puts the values back on separate rows if they are in different years and I may as well go back to the straight table or table box, export to excel, and dedup for most current testvalues… Still not sure why the formula itself isn’t working in the pivot table…