Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi experts,
I hope that you can advice me with my task. I need to caluculate availability of items on the stock.
My input data are stock quantity and requested quantity per day.
Itemnumber | Stock | RequestDate | 20121214 | 20130107 | 20130108 | 20130110 | 20130111 | 20130114 | 20130115 | 20130116 | 20130117 | 20130118 |
A | 2772 | 0 | 0 | 0 | 0 | 36 | 0 | 0 | 0 | 72 | 72 | |
B | 3843 | 0 | 0 | 0 | 0 | 0 | 21 | 21 | 0 | 21 | 84 | |
C | 140 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 28 | |
D | 0 | 0 | 0 | 0 | 108 | 48 | 144 | 12 | 168 | 0 | 72 | |
E | 2160 | 0 | 0 | 0 | 0 | 0 | 24 | 0 | 420 | 12 | 204 | |
F | 6 | 414 | 0 | 0 | 0 | 0 | 162 | 60 | 318 | 36 | 72 | |
G | 736 | 0 | 0 | 0 | 0 | 0 | 72 | 124 | 288 | 60 | 52 | |
H | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 108 | 150 | |
I | 348 | 378 | 0 | 0 | 0 | 0 | 90 | 0 | 84 | 126 | 24 |
I need to reach this output in pivot table:
Itemnumber | delay | 20130114 | 20130115 | 20130116 | 20130117 | 20130118 |
A | 2736 | 2736 | 2736 | 2736 | 2664 | 2664 |
B | 3843 | 3822 | 3822 | 3843 | 3822 | 3759 |
C | 140 | 140 | 140 | 140 | 140 | 112 |
D | -156 | -300 | -168 | -324 | -156 | -228 |
E | 2160 | 2136 | 2160 | 1740 | 2148 | 1956 |
F | -408 | -570 | -468 | -726 | -444 | -480 |
G | 736 | 664 | 612 | 448 | 676 | 684 |
H | 0 | 0 | 0 | 0 | -108 | -150 |
I | -30 | -120 | -30 | -114 | -156 | -54 |
Can anybody help me to find right expression?
Thanks in advance.
Understood about this summing upcoming order values, though as I understand it this is so as to predict the likely stock levels.
See attached for a way of doing this using the above() function. There was no need for a seperate variable as you have the starting stock figure. I have not managed to get working with the pivoting though.
Jonathan
can u post the sample file......
This seems like an accumulation of a stock balance to me, based upon Stock movements.
Your expression can test if it is the first row (date dimension):
- if it is then use a sum of the prior stock movements. I have found it easiest to calculate this using a variable, outside of the chart
- if it is not the first row, then you can accumulate by finding the above row and adding your new value.
An expression could be something like this:
if(RowNo()=1,$(ItemStatusAmount),Above(sum(StockMovement))+sum(StockMovement))
ItemStatusAmount could be calculated using a formula like:
sum(ALL if(TransDate<min(TransDate),StockMovement))
Jonathan
hi, thanks for your suggestion, that are not transactions, but not delivered customer orders, but I think formula is the same. I need to see, when the product will not be avalaible on stock.
I'll try to post qv and excel cal as well. I'm not sure with variables which contain formula at all.
Understood about this summing upcoming order values, though as I understand it this is so as to predict the likely stock levels.
See attached for a way of doing this using the above() function. There was no need for a seperate variable as you have the starting stock figure. I have not managed to get working with the pivoting though.
Jonathan
Thank you very much, it is what I was looking for. I changed expression slightly:
if(ColumnNo()=1,only(Stock)-sum(Nedodane),Before(Aggregate)-sum(Nedodane))
and now it is working as I wanted. Thank you very much.
Now I need to find a way have to cumulute all not nedelivered orders in previous dates.
Jonathan thank you very much for your help and quick response. It's almost perfect right now.