5 Replies Latest reply on Sep 6, 2011 6:52 AM by Joe Mako

    Using Calculated Field to filter data

    Drew Graham

      I have some data from a sales ledger in which some lines indicate a unique purchase, and some indicate a secondary purchase.

       

      Whether a purchase is unique or not depends on the Product Group.  If it belongs to groups 1, 3, 4 or 5 then it is a unique purchase, if it belongs to groups 2, 6, 7 or 8 then it is a secondary purchase.

       

      My data has columns of:

       

      Date Stamp | Customer ID | Product | Quantity | Unit Price | Total Amount | Product Group

       

      I think that I need to write a calculated field that I can call "# Unique Purchasers" that says:

       

      If Product Group is 1, 3, 4 or 5 then return the quantity, otherwise return 0.

       

      I am trying to calculate a Yield, which would them be Total Income / # Unique Purchasers.

       

      Am I going about this the right way?  If so, how would I form that calculated field?

       

      I have created a Group that splits the Product Groups into Unique Purchases and Secondary Purchases, and I would ideally prefer to use that to avoid having to re-write the calculated field for additional Product Groups.

       

      Thank you!