Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

philip_radovich
New Contributor II

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.

Screen Shot 2016-07-14 at 10.28.06 AM.png

Thanks,

Philip

1 Solution

Accepted Solutions
MVP
MVP

Re: Set Analysis in Pivot Table

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

5 Replies
MVP
MVP

Re: Set Analysis in Pivot Table

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
New Contributor II

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

MVP
MVP

Re: Set Analysis in Pivot Table

More like this:

FirstSortedValue(Value, AssessmentDate, 2)

and

FirstSortedValue(Value, -AssessmentDate, 2)

philip_radovich
New Contributor II

Re: Set Analysis in Pivot Table

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

MVP
MVP

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