Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
More like this:
FirstSortedValue(Value, AssessmentDate, 2)
and
FirstSortedValue(Value, -AssessmentDate, 2)
My years are displaying from the 1900's . Not sure why that is.
Years are displaying 1900s? I don't understand the relation to the current discussion? Can you may be elaborate Philip?
Best,
Sunny