I'd like to show all sales for the current year, with the exception of the selected month (if not selected than the current month will be the exception),
Assuming you have a Date field based on Year and Month, the result should be a range in set analysis:
Sum({< Date = {">=$(=YearStart(Today()))<=$(=MonthEnd(AddMonths(Today(), -1)))"} >} Sales)
That builds a range from the first day of the current year to the last day of the previous month.
It's even easier to create a flag field in the script so if Year equals to Year(Today()) and the date is less than or equal to the previous month date the value is set to 1, otherwise is set to 0. Then the expression is simplified to
Sum({< IsCurrent = {1} >} Sales)
As a general rule and best practice, do this sort of flag fields as often as possible, in order to simplify expressions and get better performance.
Thanks for the helpful answer. Can you advise on what should I change in the this set analysis if I would like to let to user choose the base month? For example, if the user will choose sale month May, than I should present the sales in the current year from Jenuary till April (without May). Field are: Sale_Month, Sale_Date. Thanks again,
I use to do that using a variable instead of a field. For example, you create a new Calendar object, using a variable vDateSelected instead of a field, that lets the user select any given month and populate it (or you can use an inputbox as well).
So in your expressions, the Date field is always depending on the selection by the user:
Sum({< Date = {'>=$(=YearStart(vDateSelected))'} >} Sales)
You can find more on set analysis and variables in this application.
.png "Miguel_Angel_Baeyens"){kind=avatar}
