Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm trying to compare financial figures from this year against the previous year, both yearly figures, and in month figures.
Ie if I select May 2015, in one column I want to see the figures for May 2015, and in the other column, I want to see May 2014.
I'm using the following formular to try and go back 12 months, but regardless of if the number is "-1" or "-12" it only goes back one month.
=Date(MonthName(Calendar.MonthName-12))
Any ideas?
I also the need to do YTD. I think this is easy(ish) as already have my FY years setup so can do a bit of set analysis to sum in the current FY.
Try,
sum({<[Calendar.MonthName]= {'$(=monthname(addmonths(max(Calendar.MonthName),-12)))'}, Year=, Month=, Quarter =>}Finacial_Data_Act.Local_Cost)
you can use your datefield and the function addmonths and Format to you you want to see
=date(addmonths(yourdatefield,-12),'MMM')
So if I put that into set analysis, I get an error.
=sum({<[Calendar.MonthName]= {'$(=MonthName(date(addmonths(Calendar.MonthName,-12)))'}>}Finacial_Data_Act.Local_Cost)
what are the values in your MonthName field
What is the format of Calendar.MonthName? The field name implies a string and it will unable to perform date arithmetic. Do you have a normal numeric date field?
I'm using the "MonthName" function that is built in. This displays it in a list box as "May 2015".
So my script that creates my calendar table is simply MonthName(c_Date) AS Calendar.MonthName,
Neil, add the following to your master calendar:
I'm assuming you have a field in your calendar that is Year + Month (= 2015 jan, 2015 feb, ect.)
autogenerate(Year + Month) as Period_ID. This will generate a list from 1 to ?? for each found value Year + Month.
Then in your set analysis add this:
sum({<Period_ID = {"=$(=max(Period_ID))"}>}Finacial_Data_Act.Local_Cost) for the month of current year
and
sum({<Period_ID = {"=$(=max(Period_ID)-12)"}, Year>}Finacial_Data_Act.Local_Cost) for the month of previous year
and
sum({<Period_ID = {"<=$(=max(Period_ID))"}>}Finacial_Data_Act.Local_Cost) for the month to date of current year
and
sum({<Period_ID = {"<=$(=max(Period_ID)-12)"}, Year>}Finacial_Data_Act.Local_Cost) for the month to date of previous year
the ,Year in in the set analysis will make sure that a year selection is ignored. This is needed because else only the current year is shown.
MonthName(addmonths(Date#(Calendar.MonthName,'MMM YYYY'),-12)) should give you previous year month name.
sum({<[Calendar.MonthName]= {'$(=MonthName(addmonths(Date#([Calendar.MonthName],'MMM YYYY'),-2)))'}>}Finacial_Data_Act.Local_Cost)
Try,
sum({<[Calendar.MonthName]= {'$(=monthname(addmonths(max(Calendar.MonthName),-12)))'}, Year=, Month=, Quarter =>}Finacial_Data_Act.Local_Cost)