Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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
Assuming you have Year as a field use:
=Sum(if(if(month<=3,Year-1&'-'Year,Year&'-'&Year+1)='2011-2012',Sales,0))
Kiran.