Case Solution to Date-Specific Action Calculation
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:
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.