Set Analysis in Pivot Table

[philip_radovich]

Hello,

I am trying to create a pivot table with 2 dates and 2 values that correspond with each test date.  I have two dimensions, Performance Items & Name, name is a row and performance item is a column. I have 4 measures. Initial Test, Initial Value, Latest Test, Latest Value. For the Initial and Latest test it is pulling a date from a field AssessmentDate. The Initial and Latest values are pulling from a field called value. The set analysis I am using for the date fields is: Min({$}AssessmentDate) As Initial and Max({$}AssessmentDate) As latest. This seems to be working correctly when comparing to the database. When I tried to do my set analysis for the value the values that are being pulled are not correct. The code I am using is Min({$<[Initial Test]={}>}Value) for initial and Max({$<[Initial Test]={}>}Value). I am not sure if that is correct but I am trying to pull the value that corresponds with the test date.

Thanks,

Philip

[sunny_talwar · Accepted solution]

May be try this:

Instead of Min({$<[Initial Test]={}>}Value)

FirstSortedValue(Value, AssessmentDate)

and

Instead of Max({$<[Initial Test]={}>}Value)

FirstSortedValue(Value, -AssessmentDate)

Note the negative sign in front of AssessmentDate when we are replacing Max expression

[philip_radovich]

Hi Sunny,

That appears to have worked!! As a side question if I were to need to pull in another  2 measures for Previous Test and Previous Value how would I go about doing that using the first sorted value? Would I do something like FirstSortedValue(Value, AssessmentDate) -1 to get the previous test and value? When I tried Max({$}AssessmentDate)-1 It took that max date and just subtracted 1 day. I was confused how to pull the date from the previous test.

Thanks,

Philip

[sunny_talwar]

More like this:

FirstSortedValue(Value, AssessmentDate, 2)

and

FirstSortedValue(Value, -AssessmentDate, 2)

[philip_radovich]

My years are displaying from the  1900's . Not sure why that is.

[sunny_talwar]

Years are displaying 1900s? I don't understand the relation to the current discussion? Can you may be elaborate Philip?

Best,

Sunny