Year To Date Calculation

[#1]

Hi All,

I have some sales data for which i want to calculate YTD. The months are expressed by numbers like Jan =1, Feb =2..and .so on. I want to calculate the total sales for the period April 2011 - March 2012. How do i go about it in my sum statement?

Regards

Anirban

[#2]

Add a new field in the script to give fiscal year. Something like

if(month<=3,Year-1&'-'Year,Year&'-'&Year+1) as FiscalYear.

Adding the FiscalYear in the list box would do the job.

Kiran Rokam.

[#3]

Hi Kiran,

Thanks for the reply. I wanted to ask you isnt there anything we could do without touching the back end script? I mean within the table can we use set analysis and get the job done.

Your views would be appreciated

Thanks

Anirban

[Jason_Michaelides]

You should define the Fiscal year in the script, then use Set Analysis in the UI to sum values for that year.

In the script:

Year(YearStart(Date,0,4)     AS     FiscalYear

In the UI:

Sum({<Date=,Month=,Year=,FiscalYear=P()>} Sales)

This will sum all sales for the currently selected/possible FiscalYear(s)

Hope this helps,

Jason

[#5]

Assuming you have Year as a field use:

=Sum(if(if(month<=3,Year-1&'-'Year,Year&'-'&Year+1)='2011-2012',Sales,0))

Kiran.