set analysis for six months

[#1]

Hi,

I have the following 2 expressions:

sum({$<OrderYear = {$(=max(OrderYear))}>} AMOUNT) // summary of sales for the latest year

sum({$<OrderYear = {$(vBudgetYear)}>} Budget) //vBudgetYear is defined as one year before of the latest year (so I'd have a full year of budget)

I need to sum the budget (second line)  only for the first 6 months (Jan to Jun - the amount of months I have in the current year)

How can I write the set analysis for this?

Thanks,

Assaf

[krishna20]

Hi Assaf,

How you are fixing budget?

I think monthly wise right? Is total budget of the year you calculating like this?

JAN + FEB+MAR+APR+MAY+JUNE+..................................DEC.

Regards

Krishna

[#3]

The budget is monthly, so I need a set analysis for OrderMonth. and not hard coded, of course.

[krishna20]

Hi,

Try this

=sum({$<Orderedyear= {'>=$(=Max(YearStart(today())))<=$(=max(monthend(today()))-6)'}>}Budget)

Regards

Krishna

[Anonymous]

Hi,

Try:

=sum({$<Orderedyear= {'>=$(=num(addmonths(today(),-6)))'}>}Budget)

Regards

Neetha

[krishna20]

Hi Assaf,

Sorry a small mistake,neetha corrected it.But take it as 7 for june month

=sum({$<Orderedyear= {'>=$(=Max(YearStart(today())))<=$(=max(monthend(today(),-7)))'}>}Budget)

Regards

Krishna

[#7]

tried this:

='sum({$<OrderYear = {$(vBudgetYear)},OrderMonth = {'>={1} <= {6}'}>}Budget)

even this won't work.....

[tcullinane]

sum({$<OrderYear = {$(vBudgetYear)}, OrderMonth = {'Jan','Feb','Mar','Apr','May','Jun'}>} Budget)

[#9]

I finally got it to work:

sum({$<OrderYear = {$(vBudgetYear)}, OrderMonth = { '>=$(v1stMonth) <=$(vlastMonth)'} >} Budget)

v1stMonth = num(month(date(39820))) // I needed January.....

vlastmonth =num(month(max(OrderDate)))

Thank you all!!