[jmcadams]

I am trying to write a CASE statement using the Formula (Numeric) field in a customer saved search that will SUM the number of cases closed in the previous month. I want this reoccuring as we are trying to associate # of cases with total sales by customer but I don’t know SQL so I’m struggling.

Here is my invalid expression:

Formula(Numeric)

Summary Type Sum

CASE WHEN {dateclosed} IS MONTH(EnterDate) = @LastMo_Month and YEAR(EnterDate) = @LastMo_Year
This is a cached copy. Click here to see the original post.

[jmcadams]

Here is the invalid expression:

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

[Bednar]

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

[jmcadams]

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

[Bednar]

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

[jmcadams]

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

[jmcadams]

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!

[egrubaugh]

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.

[jmcadams]

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

[egrubaugh]

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?

[jmcadams]

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

[Author]

Posts