Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone,
I have the following dimensions and expressions:
Dimensions:
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])
Expressions:
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?
Add [TXN Type]= to the set expression:
Sum({$ <[Transactions.Txn Date]={"$(='>=' & Date(YearStart(Max([Transactions.Txn Date])), 'MM/DD/YYYY') & '<=' & Date((Max([Transactions.Txn Date])), 'MM/DD/YYYY'))"},[TXN Type]=>} [Transactions.Amount])
This causes the Sum to be calculated for all [TXN Type].
Alternatively if you remove TXN Type from the table it would also total, it would not have to be added to the set expression in that case...
Where in the listed expressions are you showing/using the dimension 'TXN Type' ? I don't see it. Can you provide a screenshot of what the current result looks like?
It's more that I have to make TXN Type an expression...right now it's showing the following. I have Item Type filtered to only InventoryType. I'm looking to hide the TXN Type column and only show the $600 and $1550 totals...
Item Type | TXN Type | 2017 YTD Sales | 2016 YTD Sales |
InventoryItem | Invoice | $0.00 | $100.00 |
InventoryItem | Invoice | $0.00 | $200.00 |
InventoryItem | Invoice | $0.00 | $300.00 |
InventoryItem | Invoice | $250.00 | $0.00 |
InventoryItem | Invoice | $450.00 | $0.00 |
InventoryItem | Invoice | $0.00 | $500.00 |
InventoryItem | Invoice | $0.00 | $600.00 |
InventoryItem | Credit Memo | -$100.00 | -$50.00 |
InventoryItem | Credit Memo | $0.00 | -$25.00 |
InventoryItem | Credit Memo | $0.00 | -$75.00 |
$600.00 | $1,550.00 |
Would you be able to share the app so we can try to work it out?
I don't know how to do that !
Add [TXN Type]= to the set expression:
Sum({$ <[Transactions.Txn Date]={"$(='>=' & Date(YearStart(Max([Transactions.Txn Date])), 'MM/DD/YYYY') & '<=' & Date((Max([Transactions.Txn Date])), 'MM/DD/YYYY'))"},[TXN Type]=>} [Transactions.Amount])
This causes the Sum to be calculated for all [TXN Type].
Alternatively if you remove TXN Type from the table it would also total, it would not have to be added to the set expression in that case...
David, that worked well thank you!