How to generate records for the value of a cumulative stock (asofperiod)

[roland74]

Hi all,

Please help me with the following problem!

gwassenaar‌

hic‌

swuehl‌

stevedark‌

I need to calculate our stock per product per period (YYYYMM), this a cumulative figure because our stock quantity is not resetted at the beginning of the year.

I have looked into this document Calculating rolling n-period totals, averages or other aggregations and with the AsOfPeriod rollingtodate option I managed to calculate the number in stock per product per period (YYYYMM). Periods without stock are nicely generated.

So far so good, but now I have to combine this cumulative stock with the price of the product for the stock period.

The price for the value of a product is the last price of a period (YYYYMM), I have made an sql query for that and it gives back the max date of the period, year, month, the product and the price.

A price has no end date so will last untill a new price is put in. If there is a period without a price, the last known price (of a former period) has to be used.

My questions:

1. how do I combine the cumulative stock quantity per product in a period with the price that belongs to the product and period?

2. after this I need to expand this stock dashboard with a second cumulative stock expression per period per product, how do I do this? When I put a concatenate statement behind the first load stock statement it generates double records per product per period.....

I have aggregated the data already to product and period because we have about 30k of products and millions of stock- and price-mutations, but if an aggregation per product per day would make this easier, we can do that as well.

Thank you very much in advance for helping me with this!

In the attached excel on the sheet 'Goal Stock + Price' is the outcome I'm looking for. This is without the second stock expression, first things first

Regards,

Roland

Het bericht is bewerkt door: Roland Loykens Here my qvw that is showing the correct stock mutations and stock cumulation per period per product. In the editor I have placed the sql query that will fetch the prices as a comment but I do not know how and where to join the prices..... Is the use of the AsOf table overdone in this case and if so what's the best alternative?

[hic]

Try

tmpData1:
LOAD Period, Product, Purchase
FROM [Stock_Value.xlsx] (ooxml, embedded labels, table is Purchase);

Join LOAD Period, Product, Sales
FROM [Stock_Value.xlsx] (ooxml, embedded labels, table is [Sales ]);

Join LOAD Date, Period, Product, Price
FROM [Stock_Value.xlsx] (ooxml, embedded labels, table is Prices);

Data:
Load Period, Product, Purchase, Sales,
RangeSum(If(Peek(Product)<>Product or IsNum(Price),Price,Peek(Price))) as Price,
RangeSum(If(Peek(Product)=Product,Peek(Stock)),Purchase,Sales) as Stock
Resident tmpData1
Order By Product, Period Asc;

Drop Table tmpData1;

It will calculate both the Stock and the Price from the values above.

HIC

[roland74]

Hi Henric,

Thanks a lot!

It's almost working

The thing is I miss values for periods without any transactions (only 1 in my example: 201312 N0000001) but his often occurs).

Why don't you use the AsOfPeriod in this case? Or when to use the AsOfPeriod?

I had thought it was necessary to generate both a price-record and stock record for each period_product combination!

[hic · Accepted solution]

Then try the following instead:

MinMonth:
Load Date(Min(Date#(Period,'YYYYMM')),'YYYYMM') as MinMonth

   FROM [Stock_Value.xlsx] (ooxml, embedded labels, table is Purchase);
Let vMinDate = Num(Peek('MinMonth',-1,'MinMonth'));
Let vNumberOfMonths = Ceil(12*(Today() - vMinDate)/365.2425);

tmpData1:
Load distinct Product

   FROM [Stock_Value.xlsx] (ooxml, embedded labels, table is Purchase);
Load distinct Product

   FROM [Stock_Value.xlsx] (ooxml, embedded labels, table is [Sales ]);

Join Load Date(AddMonths('$(vMinDate)',RecNo()-1),'YYYYMM') as Period

   Autogenerate vNumberOfMonths ;

Join LOAD Date#(Period,'YYYYMM') as Period, Product, Purchase
FROM [Stock_Value.xlsx] (ooxml, embedded labels, table is Purchase);

Join LOAD Date#(Period,'YYYYMM') as Period, Product, Sales
FROM [Stock_Value.xlsx] (ooxml, embedded labels, table is [Sales ]);

Join LOAD Date#(Period,'YYYYMM') as Period, Product, Price
FROM [Stock_Value.xlsx] (ooxml, embedded labels, table is Prices);

Data:
Load Period, Product, Purchase, Sales,
RangeSum(If(Peek(Product)<>Product or IsNum(Price),Price,Peek(Price))) as Price,
RangeSum(If(Peek(Product)=Product,Peek(Stock)),Purchase,Sales) as Stock
Resident tmpData1
Order By Product, Period Asc;

Drop Table tmpData1, MinMonth;

The AsOf table is not necessary here.

HIC

[roland74]

Thanks a lot Henric!

This is working great

Now I have tot figure out how you did it

In what cases would you need the AsOf table then,

One last thing... (sorry)

Your code is adding a record for every period for every product.... is it possible to start with the period in which the product was first purchased or sold?

That would mean to obtain the minmonth per product per period....

[hic]

Yes, if you use the following as "seed" in the above script (and then join the purchases and sales on top of it)

Load
Date(AddMonths(StartPeriod,IterNo()-1),'YYYYMM') as Period,
Product
While AddMonths(StartPeriod,IterNo()-1)<= Today();
LOAD
Date#(Min(Period),'YYYYMM') as StartPeriod,
Product
FROM [Stock_Value.xlsx] (ooxml, embedded labels, table is Purchase)
Group By Product;

you will get exactly that. The While loop will generate all months after StartPeriod.

HIC

[roland74]

Thanks a lot Henric!!