Year to date vs Project to date

[Karl_Hart]

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

[Gysbert_Wassenaar · Accepted solution]

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

[Karl_Hart]

Thanks for the help, its worked great.

For info, I used

autonumber(MonthYear) as MonthYearCount

to generate the SerialMonth field