Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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;