topic Re: Set Analysis in Pivot Table in App Development

Set Analysis in Pivot Table

philip_radovich — Thu, 14 Jul 2016 14:30:13 GMT

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

sunny_talwar — Thu, 14 Jul 2016 14:55:35 GMT

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

Re: Set Analysis in Pivot Table

philip_radovich — Thu, 14 Jul 2016 15:10:26 GMT

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

sunny_talwar — Thu, 14 Jul 2016 15:55:47 GMT

Re: Set Analysis in Pivot Table

philip_radovich — Thu, 14 Jul 2016 17:02:53 GMT

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

Re: Set Analysis in Pivot Table

sunny_talwar — Thu, 14 Jul 2016 17:18:12 GMT

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

Best,

Sunny