Set analysis based on date selection

[swatiras]

Hi , I am trying to sum up monthly figures, based on list-box selection.

This is the expression i used : 

=Sum({$<DT_MONTHYEAR ={">=$(=Date(max([DT_MONTHYEAR])))"}>}AMOUNT)

This one works fine , but unable to write and expression to give AMOUNT sum for previous MonthYear(previous to the one currently selected)

[jyothish8807]

Hi Try this:

Assuming you have a Year field in your data model.

=Sum({$<DT_MONTHYEAR ={">=$(=Date(max([DT_MONTHYEAR])))"},Year ={"=$(=Year(max([DT_MONTHYEAR]))-1)"}>}AMOUNT)

[jonathandienst · Accepted solution]

Like this:

=Sum({$<DT_MONTHYEAR ={">=$(=AddMonths(Max([DT_MONTHYEAR]), -1))"}>} AMOUNT)

You may need to override selections if you are making date selections in another field. Example:

=Sum({$<DT_MONTHYEAR ={">=$(=AddMonths(Max([DT_MONTHYEAR]), -1))"}, Year, Month>} AMOUNT)

[swatiras]

Another issue : 

Lets say i want to find out the average based upon the number of days in the month. 

I need a set analysis expression like : 

=Sum({$<MONTHYEAR ={">=$(=Date(max([MONTHYEAR])))"}>}AMOUNT)/divide by the number of days in the MONTHYEAR selected in the 'MONTHYEAR' listbox