0 Replies Latest reply: Nov 8, 2011 10:59 AM by EC_KarlR RSS

    Value per last month, last year, etc

      I have a table containing a list of products and their sales for a specific day, which the user selects in a multibox (single value only).

       

      When a date is selected, I want three expressions. If the user selects, say, 2011-10-22 in the multibox, I want:

      1. One containing the sales for the selected day, i.e. SUM(Sales)

      2. One containing the sales for the last day in the month before the current month (2011-09-30)

      3. One containing the sales for the last day in the year before the current year (2010-12-31)

       

      I can't figure out how to calculate and assign a date in the set expression. If I create a textbox and, just to test date assignments, enter something like

       

      =CONCAT({1<Date={'=YearStart($(=date(Max(Date))))'}>} Date, ',')

       

      I get the same date as the one I have selected, not the start of the year.

       

      My expression is simply SUM({set_expression} Sales), but I can't figure out how to calculate the sales for a date in the past, where the date isn't hardcoded but instead calculated based on the currently selected date.

       

      Any help is greatly appreciated!