Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Remaining Balance per year with dynamic age grouping

Hi, I am trying to create a bar chart per  month & year of the remaining stocks per year with the age of that stocks on that particular period. The age of my stocks were computed based on the product...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

TheoC  hi! thank you for checking this, however this did not solve the problem. The total of the remaining quantity did not sum up to 0 though. The adjusted qty column measure that you have put just converted the number from negative to positive making both the sales & output both positive, hence you were able to come up to a bar chart with no negative number. However what I need is to get the balance per year before it gets 0 with the dynamic ageing groups. 

TheoC's avatar
TheoC
Icon for Community Champion rankCommunity Champion
3 years ago

Hi Anonymous 

 

I'm sorry, I do not understand exactly what you are after. 

 

Can you please manually enter the output that you want in the "OUTPUT REQUIRED" column in the below data sample.  

Entry_Type Item_No Variant_Code Location_Code Lot_No Quantity Posting_Date Expiration_Date Production Date Age by Posting Date Adjusted Quantity Cumulative Quantity End Of Month Age By Posting Date (Grouped) OUTPUT REQUIRED
Output 600R 6688 E FG10000 490 17/06/2017 14/06/2020 14/06/2017 3 490 490 30/06/2017 3 - 4 months  
Output 600R 6688 E FG10000 294 18/06/2017 14/06/2020 14/06/2017 4 294 784 30/06/2017 4 - 6 months  
Output 600R 6688 E FG10000 392 19/06/2017 14/06/2020 14/06/2017 5 392 1176 30/06/2017 4 - 6 months  
Output 600R 6688 E FG10000 392 20/06/2017 14/06/2020 14/06/2017 6 392 1568 30/06/2017 6+ months  
Output 600R 6688 E FG10000 392 21/06/2017 14/06/2020 14/06/2017 7 392 1960 30/06/2017 6+ months  
Output 600R 6688 E FG10000 392 22/06/2017 14/06/2020 14/06/2017 8 392 2352 30/06/2017 6+ months  
Sale 600R 6688 E FG10000 -196 27/06/2017 27/06/2020 27/06/2017 0 196 2548 30/06/2017 Less than 3 months  
Sale 600R 6688 E FG10000 -14 2/07/2017 2/07/2020 2/07/2017 0 14 2562 31/07/2017 Less than 3 months  
Sale 600R 6688 E FG10000 -817 3/07/2017 3/07/2020 3/07/2017 0 817 3379 31/07/2017 Less than 3 months  
Sale 600R 6688 E FG10000 -140 4/07/2017 4/07/2020 4/07/2017 0 140 3519 31/07/2017 Less than 3 months  
Sale 600R 6688 E FG10000 -401 5/07/2017 5/07/2020 5/07/2017 0 401 3920 31/07/2017 Less than 3 months  
Sale 600R 6688 E FG10000 -168 12/07/2017 12/07/2020 12/07/2017 0 168 4088 31/07/2017 Less than 3 months  
Sale 600R 6688 E FG10000 -168 27/07/2017 27/07/2020 27/07/2017 0 168 4256 31/07/2017 Less than 3 months  
Sale 600R 6688 E FG10000 -448 13/08/2017 13/08/2020 13/08/2017 0 448 4704 31/08/2017 Less than 3 months