Showing the value at Month End

[Dayna]

Hello All,

I have a list of data that shows the inventory for each date, but what I would like to do is show what the value is at the of the month..

I've tried using monthend(Date) from a load statement, but this gives me the amounts within that month.

Please find attached an example report based on what I'm trying to do, please help!

Many thanks,

Dayna

[prieper]

Give it a try with a script as fllws:

Temp:
LOAD * INLINE [
    Date, Product, Amount, Location
    01/01/2012, Part1, 100, A
    03/01/2012, Part1, 12, A
    05/01/2012, Part1, 14, A
    09/01/2012, Part1, 15, A
    31/01/2012, Part1, 20, A
    05/02/2012, Part1, 76, A
    29/02/2012, Part1, 54, A
];
Data:
LOAD
    *,
    IF(PREVIOUS(MONTHNAME(Date)) <> MONTHNAME(Date), MONTHEND(Date))    AS Monthend
RESIDENT
    Temp
ORDER BY
    Date    DESC,
    Product,
    Location;
DROP TABLE Temp;

HTH

Peter

[smoon63]

Old thread, but maybe this will help someone else. If you only want the records for EOM, try:

Temp:

LOAD * INLINE [

    Date, Product, Amount, Location

    01/01/2012, Part1, 100, A

    03/01/2012, Part1, 12, A

    05/01/2012, Part1, 14, A

    09/01/2012, Part1, 15, A

    31/01/2012, Part1, 20, A

    05/02/2012, Part1, 76, A

    29/02/2012, Part1, 54, A

];

DataTbl:

LOAD

  Product,

  Date as MonthEnd,

  Amount,

  Location

RESIDENT

  Temp

WHERE

  Date(floor(monthend(Date)), 'DD/MM/YYYY') = Date

;

drop table Temp;