Nested If Statement?

[cbaqir]

I have an NPrinting report that uses the attached chart to produce the current month data (sum(ALLOC_HRS) vs sum(ACT_HOURS). These reports run every Tuesday. The current NPrinting logic looks at =month(today()) and =year(today()) to dynamically pull the current data.

I have a new requirement that states "Report will deliver a cumulative and complete snapshot of the month actual v forecast for the individual project i.e. the report will show for the previous week(s) the cumulative actual hours up to the completion of the month. If the report is run in the first week of June it will provide the actual v forecast for the month of May in its entirety it will not start reporting on that new month"

What this means to me is the relationship between the run date and the month end of what is being reported. The idea is to get a complete snapshot of the previous months’ data… so the first report run in the new month should always be the snapshot of the previous month’s data and NOT include any data from the first week of the new month.

  For example, the first report run in the month of July will be on Tuesday, 7/7. This report needs to only contain data from the month of June and NOT contain data from July. To make is more complicated, our Fiscal year runs July-June. Right now we are in FY 2015 but in July we will be in FY 2016.

I think this piece is correct:

Old filter formula: =month(today())

New :FISCAL_MONTH_NUM – if within first week of the new month, use previous month’s data

  If((day(today())<8), month(today())-1, month(today()))

This one needs to be combined and I don't know how:

Old filter formula: =year(today())

FISCAL_YEAR_NUM – if within first week of the new month and the month is Jan-Jun, use current year else add one to the year to get the correct fiscal year

FISCAL_YEAR_NUM – if within July and not within the first week of the month, add one year to get the correct fiscal year else use current year

=If((day(today())<8) and month(today())<=6, year(today()) ,
If((Month(today())=7 and day(today()) > 7), year(today())+1
, year(today())))

Ideas?

[martin_dideriks]

   Hi Cassandra

To show the previous month, you could use this one:

Sum({$<TRX_DATE={">=$(=MonthStart(WeekStart(Today()-1)))"}*{"<=$(=MonthEnd(WeekStart(Today()-1)))"}>} ALLOC_HRS)

This will return the date from the beginning of the week (based on yesterday) - based on the weekstart, we will find the monthstart and monthend and used this as our intervals.

To find the yearstart and yearend for you fiscal year, you could use this:

YearStart(WeekStart(Today()-1),0,7)

YearEnd(WeekStart(Today()-1),0,7)

Hope this helps.

//Martin

[cbaqir]

Are you suggesting changing the expressions within the chart itself rather than the filter on the month and year in NPrinting?

I'm not sure I understand the need to do two expressions for year start and year end. Were you able to look at my qvw?

Thanks for the input!