topic Re: Opening & Closing Stock Month Wise Calculation Issue in QlikView

Opening & Closing Stock Month Wise Calculation Issue

udit_kumar_sana — Fri, 26 Jul 2013 08:29:39 GMT

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

Re: Opening & Closing Stock Month Wise Calculation Issue

Gysbert_Wassenaar — Fri, 26 Jul 2013 10:35:55 GMT

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))

Re: Opening & Closing Stock Month Wise Calculation Issue

udit_kumar_sana — Wed, 31 Jul 2013 09:15:03 GMT

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