Qlik Community

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 
Search instead for 
Did you mean: 
shri
Contributor II
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:

 

MaterialDeli. dateweekelementsReq. qtyAvail.qty
101109-01-192A-5500
101111-01-192B10510
101209-01-192A10500
101211-01-192B30530

 

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:

MaterialDeli. dateweekelementsReq. qtyAvail.qty
101104-02-196A-2508
101105-02-196A-10498
101105-02-196A-1497
101105-02-196A-2495
101105-02-196A-5490
101105-02-196A-8482
101105-02-196A-100382
101204-02-196A40570
101205-02-196A-20550
101205-02-196A-5545
101205-02-196A-10535
101205-02-196A30565
101205-02-196A10575
101205-02-196A-700-125

 

So please help!!.......

 

 

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

 

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

MaterialDeli. dateweekelementsReq. qtyAvail.qty
101104-02-196A-2508
101105-02-196A-10498
101105-02-196A-1497
101105-02-196A-2495
101105-02-196A-5490
101105-02-196A-8482
101105-02-196A-100382
101204-02-196A40570
101205-02-196A-20550
101205-02-196A-5545
101212-02-197A-10535
101212-02-197A30565
101212-02-197A10575
101212-02-197A-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
asinha1991
Creator III
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))

shri
Contributor II
Contributor II
Author

Sorry !! but that doesn't work