Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How do I display the record with the minimum date

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 TypePO #SO #Txn DateAmount
InvoiceServiceItem7FEB2017DJACOBSON6922-103/23/2017$926.40
InvoiceServiceItem7FEB2017DJACOBSON6922-103/23/2017$1,890.40
InvoiceServiceItem7FEB2017DJACOBSON6922-204/30/2017$360.52
InvoiceServiceItem7FEB2017DJACOBSON6922-203/23/2017$926.40
InvoiceServiceItem7FEB2017DJACOBSON6922-203/23/2017$1,890.40
InvoiceServiceItemP2546695304/21/2017$1,134.54
InvoiceServiceItemP2546695305/25/2017$1,134.54
InvoiceServiceItem2045184697204/19/2017$926.40
InvoiceServiceItem2045296697804/28/2017$926.40
InvoiceServiceItem2045296697804/26/2017$1,890.40
InvoiceServiceItem2045472698005/05/2017$1,890.40
InvoiceServiceItem2045610699105/18/2017$463.20
InvoiceServiceItem2045610699105/18/2017$945.20
InvoiceServiceItem2045755699505/24/2017$1,890.40
InvoiceServiceItem2045860700505/31/2017$463.20
InvoiceServiceItem2045860700505/31/2017$945.20
InvoiceServiceItem2045898701806/15/2017$926.40
InvoiceServiceItem2046419703707/06/2017$463.20
InvoiceServiceItem2046419703707/06/2017$945.20
InvoiceServiceItem2046573705207/19/2017$926.40
21 Replies
Anil_Babu_Samineni

May be this?

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Not applicable
Author

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

Anil_Babu_Samineni

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.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Not applicable
Author

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.

Anil_Babu_Samineni

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
antoniotiman
Master III
Master III

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

Not applicable
Author

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

Not applicable
Author

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

vishsaggi
Champion III
Champion III

Are you looking for this?

Capture.PNG

Expr:

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

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