Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

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?

Tags (1)
1 Solution

Accepted Solutions
dwforest
Valued Contributor

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

6 Replies
dan_sullivan
Contributor II

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?

Not applicable

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 TypeTXN Type2017 YTD Sales2016 YTD Sales
InventoryItemInvoice$0.00$100.00
InventoryItemInvoice$0.00$200.00
InventoryItemInvoice$0.00$300.00
InventoryItemInvoice$250.00$0.00
InventoryItemInvoice$450.00$0.00
InventoryItemInvoice$0.00$500.00
InventoryItemInvoice$0.00$600.00
InventoryItemCredit Memo-$100.00-$50.00
InventoryItemCredit Memo$0.00-$25.00
InventoryItemCredit Memo$0.00-$75.00
$600.00$1,550.00
dan_sullivan
Contributor II

Re: Dimension to Set Analysis

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

Not applicable

Re: Dimension to Set Analysis

I don't know how to do that !

dwforest
Valued Contributor

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

Not applicable

Re: Dimension to Set Analysis

David, that worked well thank you!