cancel
Showing results for 
Search instead for 
Did you mean: 

Create Query using data from 0IC_C03 to show stock balance

Private_Member_82764
Active Participant
0 Kudos

Dear All,

I have successfully setup 0IC_C03. I have loaded in the data in accordance with the documentation. I check the result and it seems the stock balance match with the data source.

However, my question is how can I setup a formula in the query so I can look up stock balance based on the date I put in to run the query.

For example, I loaded in the data on Oct 13, 2008. For material A, I have a key figure called Quantity total stock which is the net of receipt and issue total stock is 10,000 LB as of Oct 13, 2008.

When I run the same query using the date as of Oct 31, 2005, the quantity total stock show -10,000 LB. The stock balance of material should be 0 LB on Oct 31, 2005. I know I can't just simply create 2 key figures - one using the loading date (Oct 13, 2008) and one using the input variable date to add up together since I will have trouble if I enter Oct 13, 2008 as the input variable day because it will zero out.

Please advise. Thanks.

Accepted Solutions (0)

Answers (2)

Answers (2)

Private_Member_82764
Active Participant
0 Kudos

N

Former Member
0 Kudos

Hi,

The whole reason for using the non-cumulative key figures is to take care of this for you. If you properly initialized the cube, then this should be setup and working for you automatically.

On your query, you use the NC key figures that add the receipts and subtract the issues based on 0CALDAY. Not putting a date variable give you the reciepts and issues (Ins/Outs) up to your last load date/time.

Brian

Private_Member_82764
Active Participant
0 Kudos

Hi Brian,

Thank you for your reply. After I double check, the setting was wrong in the DTP of 2LIS_03_BX to feed the infocube 0IC_C03. it wasn't set to initial Non-cumulative for non-cumlative value in the extraction mode. after I change that and re-ran the whole process, the current and past stock balances are correct.

However. the problem happen again when I test delta update. I loaded 2LIS_03_BF delta update first to 0IC_C03 and compress with no marker update was checked. after that, I loaded 2LIS_03_UM delta update and compress with no no marker update was checked. For some reasons, the quantity total stock doesn't add up. it should be 9600 LB in total quantity stock but show 0 LB as of Oct 08. When I ran the query for Oct 06, the quantity total stock show -9600 LB. do you know why? Thanks again.