1 Reply Latest reply on Feb 26, 2013 3:09 AM by jmw

    Last day of the found set

    BeachedWhale

      Title

      Last day of the found set

      Post

           I'm trying to extract the last day of the found set but for that month. 

           Say the month has not ended, I want a daily average. The way it's set up right now, I took the last day of each month as a number field and divided the month's revenue by that number to get the daily average. This works if the month is over, however if I don't have the records because the month is not yet over, then I need to divide either by the current day, but that would not allow it to be the same calculation value for months that are over. So I'm trying to extract the last day of each month of the found set. How do I do that?

           Example:

           FOUND SET: 10/1/10, 10/2/10, 10/2/10, 10/6/10 ------> extract 6

           Thanks!

        • 1. Re: Last day of the found set
          jmw

               If the found set is a set of records selected in a certain layout and contain just that month (as in your example) you can sort by date in ascending order and then use Goto( LastRecord ) to access the last day in the found set. Then Day(date_field) would get you the number of days so far. 

               If you have the found set through a relationship in another table-occurrence, then Max(other_table::date_field) would get you the last date so Day( Max(other_table::date_field) ) would get you the number of days so far. 

               Should you have more months in your found set you might want to have a look at creating another instance of the table which only contains the records of the same month. The tables would be related to each other using a stored calculation of Month(date_field) for both tables. Then the Day( Max(other_table::date_field) ) should work for you. 

               Without knowing what tables you have, how they are related and also how you are calculating the average revenue (passend months have to be calculated only once after all) it is difficult to give more precise suggestions. 

               As an tip, even though you might not need it: you can get the last valid date of a month using the a calculation like Date( Month(date_field)+1; 0; Year(date_field) ) which basically gets day 0 of next month, which is returned as the last vaild date of this month.