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

# Dimension to Set Analysis

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?

• ###### Re: Dimension to Set Analysis

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?

• ###### Re: Dimension to Set Analysis

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
• ###### Re: Dimension to Set Analysis

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

• ###### Re: Dimension to Set Analysis

I don't know how to do that !

• ###### Re: Dimension to 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...

• ###### Re: Dimension to Set Analysis

David, that worked well thank you!