Skip to main content
Announcements
Defect acknowledgement with Nprinting Engine May 2022 SR2, please READ HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
cbaqir
Specialist II
Specialist II

Nested If Statement?

 

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?

2 Replies
martin_dideriks
Partner - Contributor III
Partner - Contributor III

   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
Specialist II
Specialist II
Author

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!