Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
May be this?
sum({$<[Transactions.Txn Type]={'Invoice'}, [Txn Date] = {'$(=Date(Min([Txn Date]),'MM/DD/YYYY'))'}>} Transactions.Amount)
I tried your suggestion, Anil, but unfortunately, the data came back with all of the rows. Thank you, though.
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.
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.
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])
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
Thanks, Antonio, but I can't control the Load as I'm using Advanced Reporting in Quickbooks which is the front end of QlikView.
Anil - should this be the formula for the Txn Date Dimension? If so, the syntax isn't working.
Are you looking for this?
Expr:
= FirstSortedValue(TxnDate, Aggr(TxnDate, TxnType, ItemType, SO#, TxnDate))
= FirstSortedValue(Amount, Aggr(Sum(Amount), TxnType, ItemType, SO#, TxnType))