Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi
Try with
firstsortedvalue() function
Hope it helps
hi Jean
PFA......i have updated the app...........
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.
I am not sure to use this correctly.
I tried FirstSortedValue( Scénario_Id, -Scenario_Id ) but it returns Null.
Could you help me ?
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.
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 ?
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
Use a nested rank function similar to
=IF(AGGR(RANK(AGGR(MAX(Scenario_ID),Project_ID),4),Project_ID)=1,Project_ID,NULL())
See attached updated qvw