# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

cancel
Showing results for
Did you mean:
Contributor II

## first sorted value function() with aggregation

How to use first sorted value function() to add two values

I have attached some data , now in this, for example

ex: Material 1011 & 1012 has week = 2 (this is the case when you can select only 1 Material )

• If i want to get the last sorted value(last value) for material 1011 i can use

Firstsortedvalue(Avail.qty,+Avail.qty) whose result is 510

• If i want to get the last sorted value for material 1012 i can use

Firstsortedvalue(Avail.qty,+Avail.qty) whose result is 530

BUT

if you select Both 1011 & 1012 Materials in filter pane The values should get Added i.e. Result = 510+530= 1040

and this is the required result but the above expression doesn't work so please guide

Table:

 Material Deli. date week elements Req. qty Avail.qty 1011 09-01-19 2 A -5 500 1011 11-01-19 2 B 10 510 1012 09-01-19 2 A 10 500 1012 11-01-19 2 B 30 530

And there is 1 more case where

firstsortedvalue (avail.qty,+avail.qty) of 1011 for week 6 is = 382 &

firstsortedvalue (avail.qty,+avail.qty) of 1012 for week 6 is = -125

& the result when i add these two , I should get  Result = 382-125 = 257, but i am not able to get it.

Table:

 Material Deli. date week elements Req. qty Avail.qty 1011 04-02-19 6 A -2 508 1011 05-02-19 6 A -10 498 1011 05-02-19 6 A -1 497 1011 05-02-19 6 A -2 495 1011 05-02-19 6 A -5 490 1011 05-02-19 6 A -8 482 1011 05-02-19 6 A -100 382 1012 04-02-19 6 A 40 570 1012 05-02-19 6 A -20 550 1012 05-02-19 6 A -5 545 1012 05-02-19 6 A -10 535 1012 05-02-19 6 A 30 565 1012 05-02-19 6 A 10 575 1012 05-02-19 6 A -700 -125

I got the result of this in previous discussion...

but there has been a case  where in , the below result is expected

 Material Deli. date week elements Req. qty Avail.qty 1011 04-02-19 6 A -2 508 1011 05-02-19 6 A -10 498 1011 05-02-19 6 A -1 497 1011 05-02-19 6 A -2 495 1011 05-02-19 6 A -5 490 1011 05-02-19 6 A -8 482 1011 05-02-19 6 A -100 382 1012 04-02-19 6 A 40 570 1012 05-02-19 6 A -20 550 1012 05-02-19 6 A -5 545 1012 12-02-19 7 A -10 535 1012 12-02-19 7 A 30 565 1012 12-02-19 7 A 10 575 1012 12-02-19 7 A -700 -125

Now since 1011 doesn't have "week 7" it should consider the latest Avail. qty value of 1011 & add it with Avail.qty of "week 7" of element 1012 .

i.e. 382+(-125)= 257

& this value should appear at week 7 on X-axis if both 1011 & 1012 elements are selected in filter pane

(on my x-axis i have weeks as dimention & on y axis i have expression : sum(Aggr(Firstsortedvalue([Avail. qty], +[Avail. qty]),[Date_D.autoCalendar.Week], elements))

(In short if we consider 2 elements & 1 of them doesn't have a week in common then that material should consider latest  Avail .Qty value and should get sum up with the Avail.qty of other element having different week)

2 Replies
Creator III

it wont sum it unless you use aggr, in your case...you need to use something like this

sum(aggr(Firstsortedvalue(Avail.qty,+Avail.qty), Material))

Contributor II
Author

Sorry !! but that doesn't work