topic Showing the value at Month End in QlikView

Showing the value at Month End

Dayna — Tue, 13 Mar 2012 15:38:29 GMT

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

Re: Showing the value at Month End

prieper — Tue, 13 Mar 2012 17:36:17 GMT

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

Re: Showing the value at Month End

smoon63 — Tue, 26 Aug 2014 05:11:52 GMT

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;