Yearly & Monthly Comparison

[nwilliams]

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.

[Anonymous]

you can use your datefield and the function addmonths and Format to you you want to see

=date(addmonths(yourdatefield,-12),'MMM')

[nwilliams]

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)

[sasiparupudi1]

what are the values in your MonthName field

[jonathandienst]

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?

[nwilliams]

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, 

[Michiel_QV_Fan]

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.

[sasiparupudi1]

MonthName(addmonths(Date#(Calendar.MonthName,'MMM YYYY'),-12))  should give you previous year month name.

[sasiparupudi1]

sum({<[Calendar.MonthName]= {'$(=MonthName(addmonths(Date#([Calendar.MonthName],'MMM YYYY'),-2)))'}>}Finacial_Data_Act.Local_Cost)

[Kushal_Chawda · Accepted solution]

Try,

sum({<[Calendar.MonthName]= {'$(=monthname(addmonths(max(Calendar.MonthName),-12)))'}, Year=, Month=, Quarter =>}Finacial_Data_Act.Local_Cost)