topic Re: Filter Rows for 1) min date and 2) user entered value in QlikView

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

jrdunson — Fri, 10 Feb 2017 22:00:08 GMT

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:

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

vishsaggi — Fri, 10 Feb 2017 22:18:11 GMT

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

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

jrdunson — Mon, 13 Feb 2017 13:32:55 GMT

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

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

jrdunson — Mon, 13 Feb 2017 13:45:20 GMT

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

6438.22

Aug

Aug-07

8/6/2007

2007

So, this is what would be correct:

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

sunny_talwar — Mon, 13 Feb 2017 13:51:19 GMT

Try the attached

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

jrdunson — Mon, 13 Feb 2017 13:51:36 GMT

Here is the spreadsheet

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

jrdunson — Mon, 13 Feb 2017 13:56:24 GMT

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

Jarrell

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

jrdunson — Mon, 13 Feb 2017 14:04:58 GMT

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 ?

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

sunny_talwar — Mon, 13 Feb 2017 14:10:57 GMT

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.

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

jrdunson — Mon, 13 Feb 2017 14:15:19 GMT

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

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

sunny_talwar — Mon, 13 Feb 2017 14:32:03 GMT

I will try to work on this as soon as I get some time as well.... If possible can you share a sample where you have same Transaction Date with Multiple amounts?

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

jrdunson — Mon, 13 Feb 2017 14:38:16 GMT

Just created a sample set... and am trying it out...

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

sunny_talwar — Mon, 13 Feb 2017 16:15:40 GMT

See if the attached helps

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

jrdunson — Mon, 13 Feb 2017 16:18:18 GMT

Hey, I'm heading to lunch EST, will look this afternoon, thanks

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

sunny_talwar — Mon, 13 Feb 2017 16:19:34 GMT

Sounds good

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

jrdunson — Mon, 13 Feb 2017 18:52:10 GMT

Sunny, thanks again...this is really helpful. your help is really appreciated .

Jarrell

ps ... I need to learn about left-side to right side set analysis... if you have any references, it would be doubly appreciated

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

sunny_talwar — Mon, 13 Feb 2017 18:57:52 GMT

This is a good place to start:

Set Analysis: syntaxes, examples