Warehouse Inventory Value By Item

Wed, 12 Jul 2017 13:44:42 GMT

I currently calculate the values of multiple storerooms by a Facility Number. Here is my script:

Value:

LOAD

FacilityNumber,

MonthStart(OrderDate)as OrderDate,

Sum(ExtendedCostPrice) as Value

Resident Value_Temp

Group by FacilityNumber, MonthStart(OrderDate);

Left Join (Value)

LOAD FacilityNumber,

OrderDate,

Num(If(FacilityNumber=Peek(FacilityNumber), Value+Peek(RunningTotal), Value), '$ 0,000') as RunningTotal

Resident Value

Order By FacilityNumber, OrderDate;

This works ok, assuming you have at least one transaction per month.

Now I'm being asked to do the same calculation but by Facility AND Item. I tried this:

Value:

LOAD

FacilityNumber,

ItemNumber,

MonthStart(OrderDate)as OrderDate,

Sum(ExtendedCostPrice) as Value

Resident Value_Temp

Group by FacilityNumber, ItemNumber, MonthStart(OrderDate);

Left Join (Value)

LOAD

FacilityNumber,

ItemNumber,

OrderDate,

Num(If(FacilityNumber=Peek(FacilityNumber) and ItemNumber = Peek(ItemNumber), Value+Peek(RunningTotal), Value), '$ 0,000') as RunningTotal

Resident Value

Order By FacilityNumber, ItemNumber, OrderDate;

The problem I'm having is what I mentioned above, if you don't have at least one transaction per month, you get 'gaps' in the data where there is no running total for that month.

Here is an example of the problem:

Item Number	Facility Number	Running Total	Revenue	Order Date
466088	4506	$744	$744	4/1/2015
466088	4506	$744	$0	8/1/2015
466088	4506	$992	$248	1/1/2016
466088	4506	$744	($248)	2/1/2016
466088	4506	$0	($744)	3/1/2016
466088	4506	$992	$992	4/1/2016
466088	4506	$1,239	$248	5/1/2016
466088	4506	$744	($496)	8/1/2016
466088	4506	$248	($496)	9/1/2016
466088	4506	$248	$0	1/1/2017
466088	4506	$496	$248	2/1/2017
466088	4506	$496	$0	3/1/2017

As you can see if the user selects a month in between the months highlighted in red (Like 12/1/2016 or 9/1/2015), they get zero for the running total, which is incorrect.

How can I modify this script so that it 'fills in' the missing months with the correct running total ? I've reviewed posts about generating missing data on this site but I can't find an answer for this specific example.

Don

Re: Warehouse Inventory Value By Item

aarkay29 — Wed, 12 Jul 2017 13:48:40 GMT

This may be helpful

Generating Missing Data In QlikView

Re: Warehouse Inventory Value By Item

Wed, 12 Jul 2017 14:58:35 GMT

I've read that, lots of good examples, however none for having multiple warehouses and multiple items in the same table where you need the value of Warehouse/Item for each date.

Re: Warehouse Inventory Value By Item

sunny_talwar — Wed, 12 Jul 2017 15:06:03 GMT

May be look at this

The As-Of Table

topic Warehouse Inventory Value By Item in QlikView

Warehouse Inventory Value By Item

Re: Warehouse Inventory Value By Item

Re: Warehouse Inventory Value By Item

Re: Warehouse Inventory Value By Item