Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
Not applicable

Straight Table - Show Totals Only

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-

8 Replies

Re: Straight Table - Show Totals Only

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

Not applicable

Re: Straight Table - Show Totals Only

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

Re: Straight Table - Show Totals Only

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?

Not applicable

Re: Straight Table - Show Totals Only

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

Re: Straight Table - Show Totals Only

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

Not applicable

Re: Straight Table - Show Totals Only

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

Re: Straight Table - Show Totals Only

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

Not applicable

Re: Straight Table - Show Totals Only

example