1 Reply Latest reply on Jun 29, 2011 2:45 PM by philmodjunk

    Case Solution to Date-Specific Action Calculation

    Nordeast

      Title

      Case Solution to Date-Specific Action Calculation

      Post

      Hello,

      I'd like to make a Case calculation (I believe this would be the best choice, other opinions welcome) that displays the sales of an item for the current fiscal year, which begins July 1. I am going about it in a way that performs one action if the current month is greater than or equal to 7, and another when the current month is less than 7. The former would display "Items sold since July 1 of the current year until the current date" if the current month is July or later, and the latter "Items sold since July 1 of the previous year until the current date" if the current month is not yet july.

      I believe I have solved the date part- a simplified "display "YES" if current month is greater than or equal to 7, and "NO" if current month is less than 7" calculation did the trick. Here is the code I have used: 

      Case (
      Month(Get(CurrentDate)) >= 7; "YES";
      Month(Get(CurrentDate)) < 7; "NO";
      )

      This seems to work, currently displaying "NO" in the field.
      Can someone help me figure out how to display the units sold value for a specific date range so I can replace the "YES" and "NO" with the sales functions? I inherited the project, and am working off of "If" calculations. I'm having trouble translating the actions to a Case function for some reason. Here is a similar calculation (that works with my database) that displays units sold from January 1 to March 31. I would like it to perform the same way, but instead from July 1 to the current date (and as part of a Case function). 

      If( InvoiceDate >= Date ( 7;1;Year(Get (CurrentDate) )) and InvoiceDate <= Date ( 3;31;Year(Get (CurrentDate) )) ; Units; 0)

      Thank you for your help! If anyone can think of a simpler way to do this, your suggestions are also more than welcome.

        • 1. Re: Case Solution to Date-Specific Action Calculation
          philmodjunk

          I'm not sure I follow all you want to do here. The If function you've posted just replaces the value of Units with zero if the current date doesn't fall in the specified range. Presumably a summary field or possibly a related record with a Sum calculation then totals the units for all such records?

          There are other approaches that do not use a calculaiton field at all, but instead use either a find or a relationship to produce a total for records dated in a specified date range. Does that sound like something that would work for you? If so, we'd need a more detailed description of how your table is set up and the type of report and/or layout you want here.

          Just modifying your given function to do exactly what you requested would change it to:

          Case ( InvoiceDate >= Date ( 7;1;Year(Get (CurrentDate) )) and InvoiceDate <= Get (CurrentDate) ; Units; 0)
          (Case and If can be used interchangeably if there are only two outcomes possible from a single boolean expression such as this.)