Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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..
Hi Jagan,
I was try your script, but the numbers accumulate all previous year 2013-2009.
and
I have a script :
sum({<DATE={">$(=addmonths(DATE,-24)) <=$(=addmonths(DATE,-12))"}, YEAR=, MONTH=>} AMOUNT
I think the script is true, because when I checked this script accumulate 12 months of Previous Year.
Btw, Thanks Sir Jagan...
Regards..
Try somthig like in Expressions:
Sum({<Date={">=$(=AddMonths(Max(Date),-11)) <=$(=Max(Date))"} >} [Measure Field] )
Hi Valdi
You can try this
=SUM({<Year={'$(vMaxYear)'},Date={'<=$(vMaxDate)'},Month=,Day=,MonthYear=>} MeasureField)
vMaxYear = max(Year)
vMaxDate = max(Date)
Regards
Rishi Beri
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.
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
Hi Pradip,
I'v tried your script, but only accumulate 1 month not 12 months,
sum({Date= <--- Date or Months?
Thanks & Regards.
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)
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.
Hi Jagan,,
I've tried your script, but the result is the difference..
Can you share sample qvw?