5 Replies Latest reply: Jul 14, 2016 1:18 PM by Sunny Talwar

# Set Analysis in Pivot Table

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

• ###### Re: Set Analysis in Pivot Table

May be try this:

FirstSortedValue(Value, AssessmentDate)

and

FirstSortedValue(Value, -AssessmentDate)

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

• ###### Re: Set Analysis in Pivot Table

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

• ###### Re: Set Analysis in Pivot Table

More like this:

FirstSortedValue(Value, AssessmentDate, 2)

and

FirstSortedValue(Value, -AssessmentDate, 2)

• ###### Re: Set Analysis in Pivot Table

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

• ###### Re: Set Analysis in Pivot Table

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

Best,

Sunny