Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

dayna_litherlan
Contributor

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
Honored Contributor II

Re: Showing the value at Month End

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

Highlighted
Partner
Partner

Re: Showing the value at Month End

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;