6 Replies Latest reply: Jun 15, 2017 10:29 AM by Nicole D'Souza RSS

    Dimension to Set Analysis

    Nicole D'Souza

      Hi Everyone,


      I have the following dimensions and expressions:



      1.  Item Type:

      =if (Match([Transactions.Item Type], 'InventoryItem') and

      Match([Transactions.Txn Type], 'Invoice','Sales Receipt', 'Credit Memo', 'Charge', 'ARRefundCreditCard'), [Transactions.Item Type])


      2.  TXN Type:

      =if(([Transactions.Inventory Table] = 0) and (Transactions.Pending = 0 or IsNull(Transactions.Pending)) and Match([Transactions.Txn Type], 'Invoice','Sales Receipt', 'Credit Memo', 'Charge', 'ARRefundCreditCard'), [Transactions.Line Items Txn Type])



      1. 2017 YTD Sales

      Sum({$ <[Transactions.Txn Date]={"$(='>=' & Date(YearStart(Max([Transactions.Txn Date])), 'MM/DD/YYYY') & '<=' & Date((Max([Transactions.Txn Date])), 'MM/DD/YYYY'))"}>} [Transactions.Amount])


      2. 2016 YTD Sales

      =Sum({$ <[Transactions.Txn Date]={"$(='>=' & Date(YearStart(Max([Transactions.Txn Date]),-1), 'MM/DD/YYYY') & '<=' & Date(addyears(Max([Transactions.Txn Date]),-1), 'MM/DD/YYYY'))"}>} [Transactions.Amount])


      I only want to show the total for the expressions, but it's listing out every single TXN Type.  I know that I need to remove the TXN Type from the dimensions, but how do I make it a set analysis?