topic Re: How do I display the record with the minimum date in QlikView

How do I display the record with the minimum date

Tue, 15 Aug 2017 18:48:36 GMT

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

Re: How do I display the record with the minimum date

Anil_Babu_Samineni — Tue, 15 Aug 2017 19:13:18 GMT

May be this?

sum({$<[Transactions.Txn Type]={'Invoice'}, [Txn Date] = {'$(=Date(Min([Txn Date]),'MM/DD/YYYY'))'}>} Transactions.Amount)

Re: How do I display the record with the minimum date

Tue, 15 Aug 2017 19:21:15 GMT

I tried your suggestion, Anil, but unfortunately, the data came back with all of the rows. Thank you, though.

Re: How do I display the record with the minimum date

Anil_Babu_Samineni — Tue, 15 Aug 2017 19:25:43 GMT

Should work, for 6922-1 which date amount you need to pick. If this condition won't work that mean your date format is not good.

Re: How do I display the record with the minimum date

Tue, 15 Aug 2017 19:31:40 GMT

For 6922-1, it doesn't matter which record is displayed. I don't care which Amount is displayed, I just need one record for each SO #. Actually, I don't even need the Amount displayed, but wasn't sure how to make the Txn Date field an expression.

Unfortunately, I can't control the format for the date as I don't have the ability to control the Load statement.

Re: How do I display the record with the minimum date

Anil_Babu_Samineni — Tue, 15 Aug 2017 19:34:59 GMT

Great, I thought you are doing this in Set analysis

May be try this

Only({$<[Transactions.Txn Type]={'Invoice'}, [Txn Date] = {'$(=Date(Min([Txn Date]),'MM/DD/YYYY'))'}>} [Txn Date])

Re: How do I display the record with the minimum date

antoniotiman — Tue, 15 Aug 2017 19:43:59 GMT

Hi Chris,

try this

SET DateFormat='MM/DD/YYYY';
LOAD [Txn Type],
[Item Type],
[PO #],
[SO #],
[Txn Date],
PurgeChar(Amount,'$') as Amount
FROM
"https://community.qlik.com/message/1325946"
(html, codepage is 1252, embedded labels, table is @1);

Regards,

Antonio

Re: How do I display the record with the minimum date

Tue, 15 Aug 2017 19:45:52 GMT

Thanks, Antonio, but I can't control the Load as I'm using Advanced Reporting in Quickbooks which is the front end of QlikView.

Re: How do I display the record with the minimum date

Tue, 15 Aug 2017 19:51:48 GMT

Anil - should this be the formula for the Txn Date Dimension? If so, the syntax isn't working.

Re: How do I display the record with the minimum date

vishsaggi — Tue, 15 Aug 2017 19:58:37 GMT

Are you looking for this?

Expr:

= FirstSortedValue(TxnDate, Aggr(TxnDate, TxnType, ItemType, SO#, TxnDate))

= FirstSortedValue(Amount, Aggr(Sum(Amount), TxnType, ItemType, SO#, TxnType))

Re: How do I display the record with the minimum date

Tue, 15 Aug 2017 20:18:49 GMT

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	-

Re: How do I display the record with the minimum date

vishsaggi — Tue, 15 Aug 2017 20:30:49 GMT

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?

Re: How do I display the record with the minimum date

Tue, 15 Aug 2017 20:42:13 GMT

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.

Re: How do I display the record with the minimum date

vishsaggi — Tue, 15 Aug 2017 20:50:50 GMT

Can you show the expressions you are using?

Re: How do I display the record with the minimum date

Tue, 15 Aug 2017 20:54:21 GMT

= 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]))

Re: How do I display the record with the minimum date

vishsaggi — Tue, 15 Aug 2017 20:55:59 GMT

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

Re: How do I display the record with the minimum date

Tue, 15 Aug 2017 21:03:30 GMT

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

Also, I do need the Set Analysis in there

Re: How do I display the record with the minimum date

vishsaggi — Tue, 15 Aug 2017 21:08:48 GMT

Can you share this sample you are working on?

Re: How do I display the record with the minimum date

Tue, 15 Aug 2017 21:12:43 GMT

I assume you mean the .qvw...

Re: How do I display the record with the minimum date

vishsaggi — Tue, 15 Aug 2017 21:16:54 GMT

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