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!