6 Replies Latest reply: Feb 22, 2012 9:00 AM by jagan mohan rao appala

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

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

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

Hi,

Try using the expression

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

Hope this helps you.

Regards,

Jagan.

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

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"?

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

Hi,

You can use the same suggested by Jagan with slight modification

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

Celambarasan

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

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.

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

Hi,

You can use like this

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

Hope it helps

Celambarasan

• ###### Re: Help with SET-analysis - values "year to period"

Hi Tobias,

please see the attached example.

Maybe its helpfull to solve your business need.

Good Luck!

Rainer