Opening & Closing Stock Month Wise Calculation Issue

[udit_kumar_sana]

Hi All,

I am  facing one problem in calculating Opening  & Closing Stock, Month Wise in Pivot/Straight Table.

The Logic For Stock Calculation is as below:-

Closing Stock:- suppose i want to get Closing stock  for current Month for  an itemcode A ,then we have to  sum the transaction Filed data 

                         from Starting date of system to max date of current Month.

Opnenig Stock:-suppose i want to get Opening stock for Current Month for an itemcode A,then we have to sum the transaction field data from starting date of

                          system to max date of Previous month

I have written below Calculation in Set Analysis:-

Closing Stock:-

sum({$<ITEM_CODE={"A"},TRANSACTION_DATE={"<=$(=num(max(TRANSACTION_DATE)))"},TRANSACTION_DAY=,TRANSACTION_MONTHNAME=>}PRIMARY_QUANTITY)

Opening Stock :-

sum({$<ITEM_CODE={"A"},TRANSACTION_DATE={"<=$(=num(min(TRANSACTION_DATE)-1))"},TRANSACTION_DAY=,TRANSACTION_MONTHNAME=>}PRIMARY_QUANTITY)

This Calculations Works Fine in Pivot/StraightTable without Month Dimension in it.

But we want with Month wise dimension  Opening & Closing Stock in Pivot /StraightTable .

Please help me out for this calculation.

Regards,

Udit

[Gysbert_Wassenaar]

Set analysis calculates the set once for the entire chart, not per row. So if you want to use Month as a dimension you can't use transaction_date in the set modifier. You'll have to use if statements instead.

sum(if(TRANSACTION_DAY=max(TRANSACTION_DAY),PRIMARY_QUANTITY))

Or you can create a flag field to mark the days that are the last day of the month:

if(TRANSACTION_DAY=monthend(TRANSACTION_DAY,1,0) as IsClosingDate

You can then use get the closing stock amounts as sum({<IsClosingDate={1}>}PRIMARY_QUANTITY) and the opening stock as above(sum({<IsClosingDate={1}>}PRIMARY_QUANTITY))

[udit_kumar_sana]

But how to get for calculation from starting date to each month end or monthstart with this logic