So I run into this same problem almost daily Someone asks for a report to show certain data (let’s say on time shipments to our customers) I build a saved search showing said data exactly Then the requester now says “great can I get this data by month for the last 12 months?” How can I do this in NetSuite? I’ve tried making a dataset in a SuiteAnalytics workbook and making a pivot table from it but the monthly columns just show as 1, 2, 3, 4 etc not Jan 21, Feb 21, Mar 21 There must be some way to do this it’s like every report I make needs this month over month analysis and I just can’t figure out how to do it outside of reports which I can’t get the data right in

@AI1 You can group in columns in saved search similar to SuiteAnswer 23079. Use case statements in your saved search to break out the month in a column

Thanks. @GeneralKenobi a very good option. We’ve tried that but it is always a stagnant thing. For instance it always shows Jan - Dec even if it’s currently July and you want it to show Jul 20 - Jul 21. Also it ends up messing with the numbers if it wraps around an entire year Just want something like the Column field in reports that will show current month and a by month previous 12 month rolling

So we have a search for sales for the last 12 months and here is the formula used for the columns Formula(Currency):CASE WHEN TO_CHAR({trandate},'MM/YY')=TO_CHAR(TO_DATE(ADD_MONTHS({today},-9)),'MM/YY') THEN {amount} ELSE 0 END: Summary Label: "9 months ago"; You just change the number of months for each column

Thanks again @GeneralKenobi we have used that as well. It does work but I guess I was just hoping for something a little more elegant or easier to manage Oh well

Once you build the report @GeneralKenobi mentioned above it is dynamic. Unless you want to change the number of moths that show. Using his formula it is a rolling report.

I appreciate all the ideas. I don’t think it will be sufficient in the end but a good start I’m sure they will want to set a date range and have it show by month between the dates Show last year month over month Other things like that. This saved search option just limits us to only the date ranges hardcoded into the formulas

You can add a filter for the users to set the date range under the "Available Filters" tab and select show in region. Just a thought

Yes but wouldn’t the date columns be hardcoded based on today’s date? Like if they set the date range from Jun 20 - Jul 21 then the Aug, Sep columns would be blank because the data ends at Jul 21 but the Aug Sep columns are based on {today} - 1 month etc Also we have a goofy fiscal calendar (5-4-4) which adds more complexity