Skip to Content
avatar image
Former Member

Last value of previous month

Hi Team,

I have requirement, where I need to get the last value of the previous month and put in all the records of the current month.

If you see in the attached screen shot, i have Income MTD which is a formula and I want to calculate Prior Month MTD. For that I need to get the last value of Prior Month and coppied to all the dates of current month.

So can someone guide me how to get last value of the prior month.

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Best Answer
    Sep 09, 2014 at 01:28 PM

    Hi Suresh,

    This is possible only in two ways

    1. Write a SQL Expression to get previous month end value

    2. Use a free hand SQL to get last year information like :

    Select product, date,value,(select value from table where date = currentdate - day(currentdate)) as lastmonth_value from table etc..

    Note : SQL expression option may not be available for few databases.

    Thanks,

    Sastry

    Add comment
    10|10000 characters needed characters exceeded

  • Sep 09, 2014 at 02:12 PM

    Hi Suresh,

    If, for some reason, you're not able to put Sastry's suggestions to use and wish to do this at the report level, here's what you need to do:

    1) Assuming the report pulls in the required data for the last month as well, create a formula (@LastValue) with this code:

    numbervar mm := Month((Minimum(MonthToDate))-1);

    numbervar yy := Year((Minimum(MonthToDate))-1);

    If {Date_field} IN [cdate(yy,mm,1) TO Minimum(MonthToDate)-1] then

    {Date_field}

    2) Create another formula (@PriorIncome) with this code and place on the Details section (you may suppress this formula):

    whileprintingrecords;

    If Maximum({@LastValue}) = {Datefield} then

    {Measure_field}

    3) Create one last formula called @PriorMTD with this code and place it on the details section:

    EvaluateAfter({@PriorIncome});

    {@Priorincome};

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded