Option 1: Create a Constituents Query
You can create a constituents query and sum the amount sold for each customer. The benefit of this solution is that you have more flexibility in the amount you can calculate and you also have the option to include discounted amounts in your calculation. 
  1. Go to Analysis > Information Library 
  2. Click Add an ad-hoc query 
  3. Select the source view of Constituents and click OK. 
  4. First, we will need to find any constituents who have made purchases during our date range. To do this, in the left column of your query, highlight the Sales Orders node. From the middle column, drag Transaction Date Include records where. Set this to be equal to your date range. 
  5. In the left column, expand the Sales Orders node, then highlight Sales Order Item. From the middle column, drag Type to Include records where. Set this to be equal to Merchandise. Your field should appear like this: Sales Order\Sales Order Item\Type is equal to Merchandise. Here is an example of how your query parameters will look: 
    User-added image
  6. Next, we will add the amount fields to Results fields to display to see a total. With Sales Order Item still highlighted on the left, from the middle column, drag Net amount to Results fields to display. If you would like to sum this amount by constituent,  click the Sigma sign and select to sum this field. 
    User-added image
  7. (Optional) If you would like to exclude any refunded transactions from this query, you can choose how to do with one of the techniques in our related solution: How to exclude refunds from a sales order query 
Option 2: Create a Smart Field 
You can also create a smart field to calculate how much a constituent has spent on merchandise at your organization. Note: This method would not consider any discounts or refunds due to the way the financial information is stored in the database. The benefit of this solution is that this field exists on the constituent record, so this value can be easily viewed and can be used in queries. 
  1. Go to Administration > Smart Fields 
  2. Click Add to add a new smart field. 
  3. When the Select a smart field definition screen pops, select the Constituent revenue application amounts smart field and click OK.  
  4. The Add smart field window will appear. On the General Tab, name your smart field. For example "Merchandise Sales"
  5. On the Parameters tab, we will specify what revenue (in this case merchandise revenue), you would like the smart field to calculate. 
    1. Under Value to return, select Total applicatiom amount
    2. Under For, select either Constituent Revenue or Household member revenue if you'd like to consider totals by household. 
    3. Next, under Revenue Types and Transaction Types/Application, leave all of the boxes unchecked. We will instead use the Application selection to tell Altru that we would like to calculate merchandise.
    4. Under Application Selection, click the magnifying glass to bring up the search window.  In the search window, click the Add button to add a new query. 
      1.  When prompted to select a source view, choose Revenue Application and click OK. 
      2. In the new Ad-hoc query window, in the middle column, drag Application to Include records where. Set this to be equal to Merchandise
      3. On the Set save options tab, name your query (for example: Merchandise revenue for Smart Field) and click Save and close. 
    5. Once you save your smart field, you should be directed back to the smart field parameters tab. Under Timeframe, select a time frame if applicable. 
  6. Click Save to save the smart field. 
  7. Click Start Process to process the values for your smart field. Note: We also recommend putting your smart field on a job schedule on the Job Schedules tab of your smart field if you would like the smart field value to update on automatically regular basis. 
  8. Once the smart field is saved, you should be able to see the values for the smart field on a constituent's record on their Smart Fields tab. You should also be able to query on the smart field using the Smart Fields folder in a constituent query. For more information about querying on a smart field value, see our related video here: Using Smart Fields in Altru Queries.