Pages

2010/03/23

Viewing Items Sold in Kits vs Items Sold Directly

It may be of interest for you to know how many items you have been selling directly versus how many items you've sold as part of a kit. The following search is a good starting point to get this information:

Item Search

CRITERIA:
Transaction: Posting = True
Transaction: Account Type = Income

COLUMNS
Formula (Text) decode({memberitem},'',{name},{memberitem}) - GROUP
Formula (Text) decode({memberitem},'','Direct','Part of Kit') - GROUP
Formula (Numeric) decode({memberitem},'',{transaction.quantity},{transaction.quantity}*{memberquantity}) - SUM

This search will output three columns:
  1. The item name
  2. If the item represents the total quantity sold directly or as part of a kit
  3. The quantity sold
So if you have a certain item that you sell directly as well as part of a kit, the item will be displayed on two separate rows - one row for the total quantity sold directly and one row showing the quantity sold as part of kits.
If you want to see the total sales amount, well, you'll have a bit of a decision to make. For direct sales it's no problem, just grab the Amount field. But items sold as part of Kits do not generate their own sales amount; the Amount is the amount of the entire kit. So you'll need to determine what the value of an item sold within a kit is. You could just grab the standard Base Price off the item record, which probably makes the most sense. If you would like to do this, add the following line to your columns:
Formula (currency) decode({memberitem},'',{transaction.amount},{transaction.quantity}*{memberquantity}*{memberitem.price}) - SUM
There are a couple of restrictions to note here:
  • This will not work if you have kits within kits
  • This search does not show the parent kits - only their members
  • This search grabs all transactions hitting Income accounts, so this includes things like credits, which would appear as negatives here
To explain the formulas a bit, you'll notice I use decode() a lot. Decode works in the following way: decode(value1, value2, value3, value4) -> IF value1 is equal to value2, THEN output value3 ELSE output value4. So what we do here is check if there is a value in a member item, and then output one value or another depending on if we're dealing with a kit or not. Following this logic, you should be able to add other fields as you want.

Hope this helps!

3 comments:

  1. May I ask for help? I'm configuring netsuite for direct selling business. I want to create save search having the following:
    1. Name of Customer
    2. Sales amount of Customer
    3. Referrer of Customer
    4. Sales amount of referrer.

    I created custom field in customer form for the name of referrer.

    And is it possible up to 5th level?

    Thanks

    ReplyDelete
  2. Netsuite only allows joins 1 level deep. So, let's say you're doing a Customer search, you can join on Tranactions to get the sales emounts, and you can then output THIS customer's referrer (because its a "local" field directly on the customer we're returning, and you can then join on this referrer to look at HIS referrer. So that bring you to 2 levels of referrer, and you can't go any further.

    I am certain a solution could be developped to track further levels, but the solution would need to be much more complex, involving Custom Records and probably some Suitescript.

    Hope that helps!

    ReplyDelete
  3. Thank you for the information :)

    ReplyDelete