Calculation YTD by month selected

[#1]

Hi Guys,

I need your help..

How to write script, if I want to calculate YTD by month selected.

for example:

if I selected year 2014 and month may then accumulation (Jun13 - May14)

or if I selected year 2014 and month apr then accumulation (May13 - Apr 14)

or if I selected year 2013 and month jun then accumulation (Jul12 - Jun13)

Please help me..

thanks & regards..

[senpradip007]

Try somthig like in Expressions:

Sum({<Date={">=$(=AddMonths(Max(Date),-11)) <=$(=Max(Date))"} >} [Measure Field] )

[#3]

Hi Valdi

You can try this

=SUM({<Year={'$(vMaxYear)'},Date={'<=$(vMaxDate)'},Month=,Day=,MonthYear=>} MeasureField)

vMaxYear = max(Year)

vMaxDate = max(Date)

Regards

Rishi Beri

[senpradip007]

Hi Rishi.

If you set Year as Max(Year) how do you get Previous Year data. This expression calculate data of a perticular year to till date.

Ex if you select year 2014 and month Jun then you would get data of 01-Jan-14 to 30-Jun-14. but the Valdi's requirement is different what you have thought.

[#5]

Hi Pradip

For previous year calculation you can use

=SUM({<Year={'$(vPriorYear)'},Month=,Day=,Date={'<=$(vPriorYearDate)'},MonthYear=>} MeasureField)

vMaxYear = max(Year)

vPriorYear = vMaxYear -1

vPriorYearDate = date(addyears(max(Date), -1), 'DD MMM YYYY')

Regards

Rishi Beri

[#6]

Hi Pradip,

I'v tried your script, but only accumulate 1 month not 12 months,

sum({Date= <--- Date or Months?

Thanks & Regards.

[senpradip007]

I thing you have just misunderstood the actual requirement. Valdi  wants if anyone selects year 2014 and month May then accumulation should be from Jun13 to May14.

Did you get the desired result by using following expressions what you have suggested?

=SUM({<Year={'$(vMaxYear)'},Date={'<=$(vMaxDate)'},Month=,Day=,MonthYear=>} MeasureField)

vMaxYear = max(Year)

vMaxDate = max(Date)

[jagan]

Hi,

Try this expression

=Sum({<Year=, Month=, Date={">=$(=MonthStart(Max(Date),-12)) <=$(=Max(Date))"} >} [Measure Field] )

Replace Year=, Month=, Date= with your actual field names.

Regards,

Jagan.

[#9]

Hi Jagan,,

I've tried your script, but the result is the difference..

[senpradip007]

Can you share sample qvw?