Dimension to Set Analysis

[#1]

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?

[dan_sullivan]

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?

[#3]

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

[dan_sullivan]

Would you be able to share the app so we can try to work it out?

[#5]

I don't know how to do that !

[dwforest · Accepted solution]

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...

[#7]

David, that worked well thank you!