Help with SET-analysis - values "year to period"

[#1]

Hi,

Have a list box (YearMonth) with reporting periods, 201109, 201110, 201111, 201112 etc. YearMonth have a logical connection to the listboxes "Year" and "Month" (When 201109 i choosen i "YearMonth", only "Year" 2011 and "Month" 09 are available (but not choosen).

The user have to choose ONE reporting period (YearMonth) i e 201109.

I would then like to calculate the monthly "Sales" and the year to period "Sales". Monthly Sales works fine with just : =Sum(Sales)

How do I calculate the Sales for period 1 to period 9?

Thanks in advance

[jagan · Accepted solution]

Hi,

Try using the expression

=Sum({<Year={'=Max(Year)'}, MonthYear=, Month={'<=$(=Max(Month))'}>} Sales)

Hope this helps you.

Regards,

Jagan.

[CELAMBARASAN]

Hi,

     You can use like this

     Sum({<Year=,Month=,YearMonth={">=$(=Num(Max(Year)&1))<=$(=Max(YearMonth))"}>} Sales)

Hope it helps

Celambarasan

[#4]

Hi Tobias,

please see the attached example.

Maybe its helpfull to solve your business need.

Good Luck!

Rainer

[#5]

Many Thanks for your reply, here is a follow-up question:

How do I make a SET expression to receive last year "sales to period"?

[CELAMBARASAN]

Hi,

     You can use the same suggested by Jagan with slight modification

     =Sum({<Year={'$(=Max(Year)-1)'}, MonthYear=, Month={'<=$(=Max(Month))'}>} Sales)

Celambarasan

[jagan]

Hi,

You can use below expression to get sales of entire previous year

=Sum({<Year={'=Max(Year)'}, MonthYear=, Month=>} Sales)

You can use below expression to get sales of previous year upto selected month

=Sum({<Year={'=(Max(Year) - 1)'}, MonthYear=, Month={'<=$(=Max(Month))'}>} Sales)

Hope this helps you.

Regards,

Jagan.