2 Replies Latest reply on Oct 9, 2010 5:41 PM by sunmoonstar.13

    Count of records in the last 30 days

    david583

      Title

      Count of records in the last 30 days

      Post

      FM Pro10, FM Pro11, FM server11. Shared Windows network, XP & Vista. Solution built on Pro10.

      We have a Helpdesk database tracking customer calls/solutions

      We have a portal showing customers sorted on the total count of open jobs they have.

      What we need is the portal showing the customers total jobs opened in the last 30 days.

      I currently have calculation fields in my Customers table (portal based on this) that calculates the total jobs, the total open jobs and (hopefully) the total jobs in the last 30 days. The one that doesn't work is listed below, I am hoping someone can see where I'm going wrong.

      Case (
      Helpdesk::Just_Date ≤ Get ( CurrentDate )
      and
      helpdesk::Just_Date ≥ (Get ( CurrentDate ) - 30 ) ;
      Count ( Helpdesk::Job_No) )

      I have also tried looking for a found count instead of counting the job number field, but that didn't seem to work any better.

        • 1. Re: Count of records in the last 30 days
          david583

          Thanks Nick,

          That works brilliantly.

          For some reason comments are not comming up on this post for me, but I did get the email notification.

          When it comes up I'll select it as the best answer. Thanks again

          David

          P.S. this one seems to come up as an answer, so for the benifit of others, this was Nick's reply. Thanks again Nick.

          posted 9:33 AM by sunmoonstar_13

          Comment:

          Try this:

          In your customers table, create an unstored Calculation field (call it, say, Date_mark ) with this calculation:

           

          Case ( Helpdesk::Just_Date ≤ Get ( CurrentDate ) and Helpdesk::Just_Date ≥ (Get ( CurrentDate ) - 30) ;1 )

           

          Then create a Summary field: Summary = Count ofDate_mark

           

          The Calculation field returns a value of 1 if the Case statement is true and then the Summary field simply counts those records "marked" with the value of 1. 

           

          Nick

           

           

          • 2. Re: Count of records in the last 30 days
            sunmoonstar.13

            I deleted my comment, because after thinking about it, I wasn't 100% sure that Helpdesk::Just_Date was the right table & field to use, but based on your response, I gather it was!

             

            Nick