How do I display the record with the minimum date

[#1]

I know that this must involve the FirstSortedValue function and possibly the Aggr function as well...

First, I don't have control over the Load as this is QlikView Advanced Reporting in Quickbooks.. 

In a Straight Table, I'm trying to display only the record for each SO # that has the earliest Txn Date. For the Amount field, I have the Expression built this way:    sum({$<[Transactions.Txn Type]={'Invoice'}>} Transactions.Amount)

So for SO# 6922-1, I'd want to see only one record since the same date displays twice. For SO# 6922-2, I'd want to see one of the records that has the 3/23/2017 date.

Txn Type	Item Type	PO #	SO #	Txn Date	Amount
Invoice	ServiceItem	7FEB2017DJACOBSON	6922-1	03/23/2017	$926.40
Invoice	ServiceItem	7FEB2017DJACOBSON	6922-1	03/23/2017	$1,890.40
Invoice	ServiceItem	7FEB2017DJACOBSON	6922-2	04/30/2017	$360.52
Invoice	ServiceItem	7FEB2017DJACOBSON	6922-2	03/23/2017	$926.40
Invoice	ServiceItem	7FEB2017DJACOBSON	6922-2	03/23/2017	$1,890.40
Invoice	ServiceItem	P2546	6953	04/21/2017	$1,134.54
Invoice	ServiceItem	P2546	6953	05/25/2017	$1,134.54
Invoice	ServiceItem	2045184	6972	04/19/2017	$926.40
Invoice	ServiceItem	2045296	6978	04/28/2017	$926.40
Invoice	ServiceItem	2045296	6978	04/26/2017	$1,890.40
Invoice	ServiceItem	2045472	6980	05/05/2017	$1,890.40
Invoice	ServiceItem	2045610	6991	05/18/2017	$463.20
Invoice	ServiceItem	2045610	6991	05/18/2017	$945.20
Invoice	ServiceItem	2045755	6995	05/24/2017	$1,890.40
Invoice	ServiceItem	2045860	7005	05/31/2017	$463.20
Invoice	ServiceItem	2045860	7005	05/31/2017	$945.20
Invoice	ServiceItem	2045898	7018	06/15/2017	$926.40
Invoice	ServiceItem	2046419	7037	07/06/2017	$463.20
Invoice	ServiceItem	2046419	7037	07/06/2017	$945.20
Invoice	ServiceItem	2046573	7052	07/19/2017	$926.40

[#2]

That looks right, but I'm getting this:

Txn Type	Item Type	PO #	SO #	Txn Date	Amount
Invoice	ServiceItem	7FEB2017DJACOBSON	6922-1	03/23/2017	-
Invoice	ServiceItem	7FEB2017DJACOBSON	6922-2	03/23/2017	-
Invoice	ServiceItem	7FEB2017DJACOBSON	6922-2	04/30/2017	-
Invoice	ServiceItem	P2546	6953	04/21/2017	-
Invoice	ServiceItem	P2546	6953	05/25/2017	-
Invoice	ServiceItem	2045184	6972	04/19/2017	-
Invoice	ServiceItem	2045296	6978	04/26/2017	-
Invoice	ServiceItem	2045296	6978	04/28/2017	-
Invoice	ServiceItem	2045472	6980	05/05/2017	-
Invoice	ServiceItem	2045610	6991	05/18/2017	-
Invoice	ServiceItem	2045755	6995	05/24/2017	-
Invoice	ServiceItem	2045860	7005	05/31/2017	-
Invoice	ServiceItem	2045898	7018	06/15/2017	-
Invoice	ServiceItem	2046419	7037	07/06/2017	-
Invoice	ServiceItem	2046573	7052	07/19/2017	-

[vishsaggi]

What are your dimensions and expressions in your straight table?

Use dimensions :  TxnType, ItemType, PO#, SO#

Use expressions: the two FirstsortedValue() functions.

Is that how you did?       

[#4]

Ok - I set it up like you said. Now I get one row for each SO# (which is good), but the values for the Txn Date and the Amount display as '-'.

Txn Type	Item Type	PO #	SO #	Amount	Txn Date
Invoice	ServiceItem	7FEB2017DJACOBSON	6922-1	-	-
Invoice	ServiceItem	7FEB2017DJACOBSON	6922-2	-	-
Invoice	ServiceItem	P2546	6953	-	-
Invoice	ServiceItem	2045184	6972	-	-
Invoice	ServiceItem	2045296	6978	-	-
Invoice	ServiceItem	2045472	6980	-	-
Invoice	ServiceItem	2045610	6991	-	-
Invoice	ServiceItem	2045755	6995	-	-
Invoice	ServiceItem	2045860	7005	-	-
Invoice	ServiceItem	2045898	7018	-	-
Invoice	ServiceItem	2046419	7037	-	-
Invoice	ServiceItem	2046573	7052	-	-

Thank you for your help on this.

[vishsaggi]

Can you show the expressions you are using?

[#6]

= FirstSortedValue( {$<[Transactions.Txn Type]={'Invoice'}>} [Transactions.Txn Date], Aggr([Transactions.Txn Date], [Transactions.Txn Type], [Transactions.Item Type], [Transactions.PO Number],[Transactions.Sales Order Number], [Transactions.Txn Date]))

=FirstSortedValue( {$<[Transactions.Txn Type]={'Invoice'}>} [Transactions.Amount], Aggr(sum( Transactions.Amount), [Transactions.Txn Type], [Transactions.Item Type], [Transactions.PO Number],[Transactions.Sales Order Number], [Transactions.Txn Type]))

[vishsaggi]

Try these?

= FirstSortedValue(  [Transactions.Txn Date],  Aggr([Transactions.Txn Date], [Transactions.Txn Type], [Transactions.Item Type], [Transactions.PO Number],[Transactions.Sales Order Number], [Transactions.Txn Date]))

=FirstSortedValue(  [Transactions.Amount], Aggr(sum( Transactions.Amount), [Transactions.Txn Type], [Transactions.Item Type], [Transactions.PO Number],[Transactions.Sales Order Number], [Transactions.Txn Type]))

[#8]

okay - the Txn Date and Amount display, but the SO# is displaying '-'.

Also, I do need the Set Analysis in there

[vishsaggi]

Can you share this sample you are working on?

[#10]

I assume you mean the .qvw...

[vishsaggi]

Its protected and asking for password. Can you remove the authentication or give me the password and reattach. ?