Set Analysis: all previous years untill selected month

[#1]

I want to select a year and a month and I want to see:

- all amounts including the full previous years untill the selected month and year. For example full 2010, full 2011, full 2012, jan 2013 and feb 2013

- all amounts untill selected month year -1. For example  full 2010, full 2011, jan 2012 and feb 2012

I have tried:

sum({$<Year = {"<=$(=max(Year))"},     Month = {"<=$(=max({<Year={$(=max(Year))}>} Month))"},>}TOTAL Amount))

But this obviously only gives me the amounts of january and february for all the years.

Any ideas?

[alexandros17]

You have to set different variables for each year you want to see and use with set analysis in columns of previous year

sum({$ <year={'$(previousyear)'}> value}

[#3]

I have tried to use a date field with the monthend function but this didn't give any result

sum

({$<PostingDate={'<=$(=MonthEnd(makedate(Max(Year),max({<Year={$(=max(Year))}>} Month)))'}>}TOTAL Amount)

The MonthEnd function on itself gives a correct date but the result is ' - ' when used in my setanalysis expression

[perumal_41]

HI,

is possible post sample application.

[#5]

Added my qvw file in first post

[Gysbert_Wassenaar · Accepted solution]

Try this: sum({$<Year=,Month=,PostingDate={"<=$(=MonthEnd(max(PostingDate)))"}>}TOTAL Amount)

See attached qvw

[#7]

It works! Thank you very much