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..
Well Done,
I've tried,
=sum({<Year=, Month=, Date={">=$(=MonthStart(Max(Date),-11))<=$(=Max(Date))"}>} [Measure Field])
Hi,
Try this expression
=Sum({<Year=, Month=, Date={">=$(=MonthStart(Max(Date),-12)) <=$(=Date(Max(Date)))"} >} [Measure Field] )
Replace Year=, Month=, Date= with your actual field names.
Regards,
Jagan.
=SUM({<YearMonth={'>=$(VminYear) <=$(VmaxYear )'}>} Measure)
VmaxYear = GetFieldSelections(Year) & GetFieldSelections(Month)
VminYear = Date(Addmonths(Date#(VmaxYear ,'YYYYMM'),-11),'YYYYMM')
Regards,
Prabhu
Can you share sample qvw?
Hi Jagan,
if I use a script -12 then accumulated to 13 months, but if I use the script -11 then accumulated to 12 months (correct).
Yes Valdi.
Regards,
Jagan.
Hi Jagan,
I need your help, how to write script if I want to see Prior Year with script YTD:
=sum({<Year=, Month=, Date={">=$(=MonthStart(Max(Date),-11))<=$(=Date(Max(Date)))"}>}[Measure Field])
Hi,
This will give you the sum of measure for the last 12 months.
=sum({<Year=, Month=, Date={">=$(=MonthStart(Max(Date),-11))<=$(=Date(Max(Date)))"}>}[Measure Field])
For example, your max date is jun-2014, then the above exp will you sum of Measure for Jul-2013 to Jun-2014.
Regards,
jagan.
Hi Jagan,
How about sum Previous Year?
Hi,
If you want from Jan-13 - Dec-13, then use this
=sum({<Year=, Month=, Date={">=$(=YearStart(Max(Date),-1))<=$(=YearEnd(Max(Date), -1))"}>}[Measure Field])
Hope this helps you.
Regards,
Jagan.