Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I want to have a report that shows the YTD spend of a project and the full spend of the lifetime (Up to a specified month) of the project
So, assume the project started in Apr-13 and I wanted to know the spend to Jan-15, I want to be able to select the Year 2015 and the month Jan and the report to show the following:-
YTD spend
= all costs from current (financial) year
I have done this in set analysis by using
{<FiscalMonthNumeric = {"<=$(=max(FiscalMonthNumeric))"}, Month=>}
Project to Date spend
I want this to show all costs from Apr-13 to Jan-15 (i.e. All months from 2013-2014 year plus Apr-Jan from 2014-2015 year)
How can this be done using set analysis?
I know I could use {<FiscalYear=, Month=>} but this would give me all costs to the current date, rather than a specified month (Jan in this case)
Any pointers would be very much appreciated
Karl
For cases like this it's useful to have a date field or a serial month field that doesn't reset when a new year starts.
You can then use expressions like:
YTD with Jan-15 selected:
sum({<FiscalMonthNumeric = {"<=$(=max(FiscalMonthNumeric))"}, Month=>}AmountSpend)
PTD with Jan-15 selected:
sum({<MyDateField = {"<=$(=max(MyDateField))"}, FiscalYear=,Month=>}AmountSpend)
or
sum({<_SerialMonth = {"<=$(=max(_SerialMonth))"}, FiscalYear=,Month=>}AmountSpend)
You could create the _SerialMonth field in the script with something like Year*12+Month
For cases like this it's useful to have a date field or a serial month field that doesn't reset when a new year starts.
You can then use expressions like:
YTD with Jan-15 selected:
sum({<FiscalMonthNumeric = {"<=$(=max(FiscalMonthNumeric))"}, Month=>}AmountSpend)
PTD with Jan-15 selected:
sum({<MyDateField = {"<=$(=max(MyDateField))"}, FiscalYear=,Month=>}AmountSpend)
or
sum({<_SerialMonth = {"<=$(=max(_SerialMonth))"}, FiscalYear=,Month=>}AmountSpend)
You could create the _SerialMonth field in the script with something like Year*12+Month
Thanks for the help, its worked great.
For info, I used
autonumber(MonthYear) as MonthYearCount
to generate the SerialMonth field