Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
shri
New 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
Contributor III

Re: first sorted value function() with aggregation

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

Re: first sorted value function() with aggregation

Sorry !! but that doesn't work