Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Dayna
Creator
Creator

Showing the value at Month End

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

2 Replies
prieper
Master II
Master II

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
Partner
Partner

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;