Bulding a Pivot Table with an expression that satisfy a condition such as 'Column = max(Column)'

[#1]

Hi,

I've got a table like this :

Project_Id     Scenario_Id          Value

----------     ------------         --------------------

1000           1                    12

1000           2                    13

1000           3                    14

2000           1                    12

2000           2                    15

I woul like to build a pivot table that returns this :

Project_Id (Dimensions)      Value(Expression)

-----------------------      ------------------------------

1000                         14

2000                         15

where Value(Expression) = sum( if( Scenario_Id = Max(Scenario_Id in the contexte of the dimension Projetc_Id), Value ) )

But It does not work.

I tried to place a calculated dimension like aggr( max(Scenario_Id), Project_Id) a then an expression like sum( Value ) but my expression is not evaluated in the context of this new dimension.

What can I do ?

Is it possible to solve my request in QlikView ?

With many thanks,

Oomingmak.

[MayilVahanan]

Hi

Try with

firstsortedvalue() function

Hope it helps

[Anonymous]

hi Jean

                   PFA......i have updated the app...........

[#4]

Than you for your answer.

The Max(Scenario_Id) cannot be loaded in script because It depends on a variable (a date) filled by the user.

Sorry I did not precise.

The max must necessary be dynamicly be evaluated in an expression.

Oomingmak.

[#5]

I am not sure to use this correctly.

I tried FirstSortedValue( Scénario_Id, -Scenario_Id ) but it returns Null.

Could you help me ?

[Gysbert_Wassenaar]

You want to get the Value corresponding to the highest Scenario_ID per Project_ID. So try:

FirstSortedValue(Value,-Scenario_Id)

See attached qvw for how the firstsortedvalue function works for your example.

[#7]

OK, thank you for your example.

I simplified my example to post it on QlikCommunity.

In my table, I've got multiple occurences of the same Project_Id and Scenario_Id and I would like to sum the values of these occurences.

In my example, if I Have

1000     1     10

1000     2     20

1000     3     30

1000     3     40

I would like to obtain :

1000      70 (= 30 +40)

Expression like FirstSortedValue(Sum(Value),-Scenario_Id) does not work

Do you think I can still use FirstSortedValue in ma case ?

[#8]

I did not understood what is your actual issue......if you want to have max value for the project_id you can use two functions

LastValue(Value)

MaxString(Value)

Thanks

[#9]

Use a nested rank function similar to

=IF(AGGR(RANK(AGGR(MAX(Scenario_ID),Project_ID),4),Project_ID)=1,Project_ID,NULL())

[Gysbert_Wassenaar · Accepted solution]

See attached updated qvw