Here is the invalid expression:

CASE WHEN {dateclosed} IS MONTH(EnterDate) = @LastMo_Month and YEAR(EnterDate) = @LastMo_Year THEN 1 ELSE 0 END

If you’re building this out in the UI, could you do this and group it however you’d like (by customer):

Criteria:

Case : Date Closed is within Last Month

Results:

Summary Type: Count

Field: Case : InternalID

I can’t do this in Criteria because I need all Customers with Sales in the previous month in the report. If I put case Date Closed in the criteria only customers with cases closed in previous month will be returned.

My Criteria:

Transaction Type: Cash Sale

Transaction Date: within last month

Originally posted by jmcadams

View Post

I can’t do this in Criteria because I need all Customers with Sales in the previous month in the report. If I put case Date Closed in the criteria only customers with cases closed in previous month will be returned.

My Criteria:

Transaction Type: Cash Sale

Transaction Date: within last month

Ah. In that case you might be able to Sum it like this if you’re running it as a customer search:

CASE WHEN (TO_CHAR({case.closed}, ‘MON-YYYY’) = TO_CHAR(add_months({today}, -1), ‘MON-YYYY’)) THEN 1 ELSE 0 END

Or use Count:

CASE WHEN (TO_CHAR({case.closed}, ‘MON-YYYY’) = TO_CHAR(add_months({today}, -1), ‘MON-YYYY’)) THEN {case.internalid} END

ok that works! Thank you! But when I add this to the results it throws off the Transaction: Amount Sum column number. Do I need a second Formula Text field in order to display sum of invoice totals in previous month?

It is not easy to cross reference different record types in Netsuite..at least I have not been successful.

Results:

Company Name Group

Transaction: Amount Sum

Formula (Numeric) CASE WHEN (TO_CHAR({case.closed}, ‘MON-YYYY’) = TO_CHAR(add_months({today}, -1), ‘MON-YYYY’)) THEN 1 ELSE 0 END

So this seems like a bug to me but if anyone has any suggestions on how to get around this I’m all ears.

Saved Customer Search

Criteria

Name/id: Customer ABC

Transaction Type is Invoice

Transaction Date is within last month

Results

Company Group

Transaction Amount Sum

Data returned is correct (good)

Customer ABC

13,695.84

But when I add either to Results after Transaction Amount Sum :

Formula(Numeric) Sum

CASE WHEN (TO_CHAR({case.closed}, ‘MON-YYYY’) = TO_CHAR(add_months({today}, -1), ‘MON-YYYY’)) THEN 1 ELSE 0 END

OR

Formula(Numeric) Count

ASE WHEN (TO_CHAR({case.closed}, ‘MON-YYYY’) = TO_CHAR(add_months({today}, -1), ‘MON-YYYY’)) THEN {case.internalid} END

The Transaction Amount Sum is now $25,268,824.80!

I can’t really test this myself, but I would guess you are getting duplicate results either due to transaction lines or customer sublist lines. Try adding another filter to your criteria for Transaction: Main Line is Yes and/or Customer (Main Line) is Yes. You should also be able to drill down into your grouped results to see exactly what is going into the $25M calculation.

Thank you for the suggestion but now the search times out.

Odd. If you revert it back so that it doesn’t time out, what are the results when you drill down into a single Customer?

No Search Results Match Your Criteria.

Criteria:

Name/ID is ABC Customer

Transaction: Type is Invoice

Transaction: Date within last month

Results:

Company Name Group

Company Name

Transaction: Type

Transaction Date

Transaction: Amount

Transaction: Amount (Sum)

Formula (Numeric) Count CASE WHEN (TO_CHAR({case.closed}, ‘MON-YYYY’) = TO_CHAR(add_months({today}, -1), ‘MON-YYYY’)) THEN {case.internalid} END