Open and Close values for each month.

[#1]

Hi all,

Have been trying to figure this out for a while. I have following load script

TEMP_HIST:

LOAD ID,

    [Date],

    Year([Date]) as Hist.Year,

    Month([Date]) as Hist.Month,

    Style,

    Color,

    Size,

    Bucket,

    Units,

    Dollars,

    Markdown,

    Deval,

    Style & '_' & Color & '_' & Size & '_' & Bucket as Hist.Key ;

SQL SELECT *

FROM StockOnHand;

and I want to generate table where Opening and Closing Qty are calculated from Units. For each month the Closing Qty is sum(Units) and Opening Qty is the colsing Qty for previous month. I tried using Order By and Sort by so that I can use FirstSortedValue(Units, -Date) but thats not working. Is there an easlier way to get this.

Is it possible to used a set analysis to almost reference the previous month for each of the months. For example if Month is dimension then in expression have something like sum({1<Month = {$(=addMonth([Date],-1))}, Month=>} Units)?

	Month	Jan	Jan	Feb	Feb	Mar	Mar	Apr	Apr	May	May	Jun	Jun
Year	Style	Opening	Closing	Opening	Closing	Opening	Closing	Opening	Closing	Opening	Closing	Opening	Closing
2012	7184N_179_M-M_2	504	605	605	504	504	349	349	504	504	203	203	504
2012	7184N_989_XL-TG_4	252	222	222	252	252	100	100	252	252	32	32	252
2012	7184N_Y57_M-M_2	359	34	34	359	359	233	233	359	359	33	33	432

Thanks,

G

[#2]

Use above(Closing) for opening value.

Regards,

Kiran Rokkam.

[#3]

Hi Kiran,

This doesn't work when I create a table with sums. They Hist.Key not used as dimension. Essentially I'm trying to whats in the image.

G

[#4]

Grifter,

If you have only one record every month, just add one more field (Opening_units) in the script for getting the opening balance as last month closing balance. This would give more flexibility for writing closing/ opening inventory expressions.

PS: I am not able see any image.

Kiran.

[#5]

Hi,

The problem is there are multiple records I tried to create a Hist.Key that would make the records uniq and I think it does. However I'm not sure how I can get the previous months value in the load script.

Grif

[#6 · Accepted solution]

Hi try the below script:

TEMP_HIST:
LOAD ID,
    [Date],
    Year([Date]) as Hist.Year,
    Month([Date]) as Hist.Month,
    Style,
    Color,
    Size,
    Bucket,
    Units,
    Dollars,
    Markdown,
    Deval,
    Style & '_' & Color & '_' & Size & '_' & Bucket as Hist.Key ;
SQL SELECT *
FROM StockOnHand;
Join
TEMP_HIST:
LOAD ID,
    AddMonths([Date],1) as [Date],
    Year(AddMonths([Date],1)) as Hist.Year,
    Month(AddMonths([Date],1)) as Hist.Month,
    Style,
    Color,
    Size,
    Bucket,
    Units as OpeningStock,
    Dollars,
    Markdown,
    Deval,
    Style & '_' & Color & '_' & Size & '_' & Bucket as Hist.Key ;
SQL SELECT *
FROM StockOnHand;

If Oracle is the backend, lag function which is easier to maintain. It goes like this:

Select date, units as ClosingStock,lag(units,1,0) over (partition by style,Color,Size order by style,Color,Size,date) as OpeningStock from StockOnHand;

Kiran Rokkam.

[#7]

Thanks Kiran. The data is coming from an Access DB. I will try to load and let you know how it goes.

Best regards,

G

[#8]

That did the trick! Thanks!

G