0 Replies Latest reply: Jan 31, 2013 8:27 AM by Peter Alvestål RSS

    sum sales on last day of months - before seleeted month

      Hi,

       

      I need to sum the sales of all last day of 12 months before and including the month I have chosen.

       

      So if I choose Mars 2012 it should give me sales of last day for Mars 2012 and 11 preceding months.

       

      With this

       

      SUM({$< Date = {'=Date=aggr(nodistinct MaxString(Date),MonthYear)'}, MonthYear = {">=$(=Date(monthname(Max(MonthYear),-11),'MMM-YYYY'))<=$(=Max(MonthYear))"}>}Sales)

       

      I get what I want as long as I dont make a selection, so I will get the end of month-sales for 12 months up to the current one.

       

      But if I choose Mars 2012 I will only get the result for Mars 2012 and nothing else. I understand that it behaves like that because of the aggr-function that will only give me a Date for the MonthYear I have chosen, but how do I do to get the Dates for all months no matter the selection and then use the MonthYear-definition like I have done above.

       

      Or better still, use only the Date-definition but in it also make sure to only get the 12 months including and before the current selected.

      Kind regards

      Peter