Filter Rows for 1) min date and 2) user entered value

[jrdunson]

I want to filter  for rows, where 1) theTransaction Date is the minimum date and 2) where the Amount is >= to a selected amount.   That is, to filter a table, for the minimum date where a specific amount occurred for the first time.  I want a user to select the min amount (I have this as a variable, vAmtMin) and then show (and do summations) for the first row (earliest date), where this amount occurred.

For example, if a user selects a Transaction Amount of $20,000, then the table should show  only the row where this occurred for the first time ...(perhaps using  min(Transaction Date), or FirstSortedValue) ...and the counts (of IDs) and totals (for Amounts) can be listed, or displayed in a chart

...(e.g. "First Time Buyers, by Year, that have paid [20,000]  ...or whatever level the users wants to see)

I’ve been able to come up with the above expressions, but not sure how to show only the minimum-date row only.   Min, and FirstSortedValue show all rows... For instance, in the attached .qvw, I want to show only the minimum-date row:

[vishsaggi]

Check Attached and let me know if this is what you are looking for ?

[jrdunson]

Hey, I'm re- looking at this and will let you know

[jrdunson]

Hey, almost... but some numbers are off: 

Here is what >= 5000 shows:

For ID 2, the first date where they had an amount >= 5000 is

2

6438.22

Aug

8

Aug-07

8/6/2007

2007

So, this is what would be correct:

[sunny_talwar · Accepted solution]

Try the attached

[jrdunson]

Here is the spreadsheet

[jrdunson]

Thank you Sunny, that seems to work well...

Jarrell

[jrdunson]

Just a follow up question

You use Aggr(Only({$<Amount = {">=$(vAmtMin)"}>} [Transaction Date]), ID, [Transaction Date])

How would I handle if, if a person gave twice on the same date...and, I wanted the total for that date... ? Say, for instance, there were two rows... with amounts of $2000, and $4000, for the same ID, on the same date...and a user entered a search for >= 5000  I'd like the row to show up, with a total of $6000 ? 

[sunny_talwar]

If that is the case, then the above won't give you any issue, but your set analysis will. Because you are checking Amount = {">=$(vAmtMin)"} and not Sum(Amount). But, since you cannot use functions or aggregations on the left hand side of the set modifier, you will have to use another aggr function (or modify your script a little bit) to get this done. It will take some time to come up with the exact expression, but if that is what is needed, we can spend some time in making it work.

[jrdunson]

I'll need to do this...The aim is to show the earliest date a person had over a specified amount... so this means a total per date... I'll try to take a crack at it...  Thanks