8 Replies Latest reply: Jun 14, 2017 12:32 PM by Nicole D'Souza RSS

    Straight Table - Show Totals Only

    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.  Is there any way to consolidate this to only show the YTD 17 and 16 totals by Item Type?


      Thank you-