Discussions
Join the NetSuite community to innovate, connect, and discover what’s next.
SuiteWorld brings thousands of innovators, builders, and leaders together to learn, connect, and shape what’s next. This October, explore how to build a stronger foundation for growth through inspiring keynotes, major product reveals, hands-on sessions, and unforgettable moments—all in one place for our biggest event of the year. Register now
Using a Formula to Show Only Data for a Fiscal Quarter in a Column
I have built a search for a client that has the date criteria built into the Columns (Results tab). For example, the first column shows Period to Date, the next column Quarter to Date, next YTD and last previous YTD. The Period to date column works fine since you can just choose a period and the YTD columns work ok because even though this client uses fiscal periods and quarters, their year still runs 1/1 - 1/31.
The issue is the QTD column. Ideally, any time you would run the report, it would pull Period to date, QTD and YTD data as of the date you are running the report; we'd prefer not to have to change any dates manually. The parameter we were using as part of a CASE WHEN statement to try and only get QTD info in a column was TO_CHAR({trandate}, 'Q') = TO_CHAR({today}, 'Q'. This would work great if they were on calendar months. But since they are on fiscal periods, this formula misses any dates that are part of the next period,