Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

How to get beginning and ending inventory using Set Analysis?

Say that I have these tables:

SELLING CALENDAR: (Fiscal Calendar)

MONTH YEARSTART DATEEND DATE
NOV-201110/26/201111/25/2011
DEC-201111/26/201112/25/2011
JAN-201212/26/20111/25/2012
FEB-20121/26/20122/25/2012
MAR-20122/26/20123/25/2012

ITEM INQUIRY: (This is the daily inquiry of available Quantity)

DATE INQUIRYITEMAVAILABLE
12/25/2011ITEM A55
1/25/2012ITEM A90
2/25/2012ITEM A107
3/25/2012ITEM A141

PURCHASE ORDER PLAN: (This is only plan quantity)

PLAN DATEITEMORDER PLAN
1/20/2012ITEM A50
2/15/2012ITEM A40
3/10/2012ITEM A45

RECEIVED ORDER: (This will be based on order plan (sometimes delivered are less, sometimes more than the ordered - but this is ok, dont mind the QTY)

RECEIVED DATEITEMRECEIVED QTY
11/25/2011ITEM A10
12/24/2011ITEM A30
1/20/2012ITEM A45
2/16/2012ITEM A40
3/11/2012ITEM A50

ACTUAL SALES: (This is actual sales)

INVOICE DATEITEMINVOICE QTY
12/23/2011ITEM A5
1/25/2012ITEM A10
1/30/2012ITEM A23
3/20/2012ITEM A16

BELOW IS WHAT I WANTED TO HAVE:

Beginning Inventory: FORMULA: Last Month Ending Inventory or Last Month Selling Date in Item Inquiry

                              -but what I want here is that, as much as possible there's no use of above() functions, I want to put exactly what's last month ending                                inventory formula, so that when I select only one month, I still get the actual beginning inventory. I don't want the BEGINNING                               INVENTORY turns 0 when only one month is selected.

                              Say for example: when month is January, the result should get

                              DECEMBER BEGINNING INVENTORY - DECEMBER ACTUAL SALES + NOVEMBER RECEIVED ORDER

                              - so that the result will be fix and not change to ZERO when only one month is selected, but if there's no way to get that, then let's straight                               forward, BEGINNING INVENTORY will be LAST MONTH ENDING INVENTORY

                              Beginning Inventory is the available balance (see item inquiry)  for last ending date of sales (see selling calendar)

                             

                              Is it applicable here to apply set analysis?

Ending Inventory: FORMULA: This Month Beginning Inventory - Actual Sales + This Month Received Order

Actual Sales: FORMULA: This Month Actual Sales

Purchase Order Plan: FORMULA: This Month Order Plan

Received Order: FORMULA: This Monthh Received Order


JAN-2012FEB-2012MAR-2012
Beginning Inventory5590107
Actual Sales102316
Purchase Order Plan504045
Received Order454050
Ending Inventory90107141

January Beginning is: December ending or Last selling date of December

                                55

January Ending is:     Jan Beginning - Jan Actual Sales + Jan Received Order

                                55 - 10 + 45 = 90

Februay Beginning is: Jan Ending Beginning or should it be possible to apply the full formula of getting JANUARY ending?

So the question is: how can I get Feb as 90 without using above() function? is it possible? Can I able to comeup this? Or please help, either way, as long as this can be resolve.

Thanks,

Bill

Tags (1)
1 Reply
Not applicable

Re: How to get beginning and ending inventory using Set Analysis?

Hi

My point of view is that is not an issue to be solved using set analysis. I would try to solve it in the script and save the results directly to a qvd file. For example:

Lets say that you need to build the rolling qty per product while you read the initial table you should build

an accumulation key like the following one in order to build break levels:

Load company & '|' & product & '|' & Year AS AccumulationKey, PeriodQty as Qty_trans...

Then you sould reference to that resident table and build opening and closing figures with the following technique:

           

FinalStock:

LOAD

product,

     Year,   Month,  AccumulationKey, Qty_trans,

if(rowno() = 1 OR AccumulationKey <> peek(AccumulationKey), 0, peek(ClosingQty)) AS OpeningQty,

if(rowno() = 1 OR AccumulationKey <> peek(AccumulationKey), Qty_trans, peek(ClosingQty) + Qty_trans) AS ClosingQty,

        if(rowno() = 1 OR AccumulationKey <> peek(AccumulationKey), Qty_Weight, peek(ClosingWeight) + Qty_Weight) AS ClosingWeight,

RESIDENT

TempSum

;

On the above code for the 1st row the opening amount is 0 and whenever there is a break level

on accoumulation key the opening amount is set to 0 again. Into the same level period qty is accumulates to closing quantity which becomes the opening qty of the next month.

I hope it helps...

Community Browser