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)
