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
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
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)
