Straight Table - Show Totals Only

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

Thank you-

[sunny_talwar]

Why do you add TXN Type as dimension, if you don't want your expression to be aggregated at that level? Is this for restricting purposes? You can use set analysis to do that

[#3]

I've added TXN Type as a dimension because I do need it in order to limit the sales that are showing, but I don't want to display it.  How do I use set analysis to do that?  I'm very new to QB and this is above my head...

[sunny_talwar]

Like I mentioned, you might be able to restrict this using set analysis, but it will be sort of difficult to provide a correct expression just by looking at the expression and dimension. Would you be able to share a sample to test out?

[#5]

So I've limited the Item Type to only show InventoryItem and I get something like the below sample.  I'm only interested in seeing one line the InventoryItem total showing 2017 YTD Sales of $600 and 2016 YTD Sales of $1,550, but I can't figure out how to hide the remaining rows.  Hope this helps you understand my issue!

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

[sunny_talwar]

I am going to tell you one more time... you will have to remove TXN Type as dimension and use the condition within it as set analysis. I don't know how else to do this. I am sorry, but may be someone else can help you

[#7]

Yeah Sunny, I can agree with you but HOW do I do the set analysis???

[sunny_talwar]

For this I would need to look at a sample... would you be able to provide one?

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

[#9]

example