Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Find first activity after given date..

Hello all,

The following shows some data I have - I want, for each unique account number (there are many!), to find the LATEST value of "ModOSD" (start date), which is pretty easy, but then I want to find the first entry in "ModernDate" which is greater than or equal to the start date, AND with Activity Type "C". I tried messing around with "IF" constructions e.g. if(ModernDate>=ModOSD,if(Activity_Type='C',ModOSD)) --- side note: is there an "AND" construct? -- but to no avail. So, in the table below, the first activity I would like to show under "Modern Date" is "5/16/2012". Note that filtering by ActType = 'C' doesn't work, since it would show the activity on 2/9/2012, which is clearly earlier than ModOSD. Any ideas?

QVdate.png

1 Solution

Accepted Solutions
Not applicable
Author

Hi,

In your chart the field for second last is NULL, because there are two lines of data which match this criteria and FirstSortOrder function returns NULL if there are more than one.

Pls try the following:

=firstSortedValue(Distinct OnDate,-OnDate,2)

best regards,

http://quickdevtips.blogspot.com

View solution in original post

9 Replies
Not applicable
Author

As another aside - where are the instructions on how to use this comment editor? I tried to copy my table's image from my QV file, but nothing happened when I tried to paste it in, so I had pasted to a bitmap file instead, which is hard to read.

Not applicable
Author

Hi,

Your IF statement looks OK to me. Can you provide the qlikview file?

And a quick question: in the resulting chart/table, do you need to see every line whose ModernDate is greater than start date, or only the first date  whose ModernDate is greater than start date?

Regards,

http://quickdevtips.blogspot.com

Jason_Michaelides
Luminary Alumni
Luminary Alumni

You could probably use FirstSortedValue() with Aggr() for this. It would help if you could post a sample app.

Not applicable
Author

Where do I go for instructions on how to create sample aps, add them to these posts (along with snapshots of images, etc.)? I'm sorry to seem stupid, but I didn't see any FAQ for this type of stuff. Thanks.

Not applicable
Author

Regarding to attaching sample app:

When you click reply, you have 'Use Advanced Editor' option on the top-right, this is where you can upload/attach a file to your reply.

Regards,

swuehl
MVP
MVP

Here are some ideas that came to my mind regarding creating a sample app.

  • A sample app should demonstrate your issue, but focus on the one issue we discuss here (in an ideal world).

  • IMHO, it is best to supply the data as INLINE table loads, or using autognerate. Second best is to supply text files / excel files. In both cases, we can do reloads easily and can also work / modify the data model.

  • Try to create a rather small app. I've seen sample apps of several 10 MB, but I think in almost any cases, you can shrink the actual data volume pretty down, and still be able to demonstrate your issue.
    If you can't create smaller sample files as input, use data reduction in the frontend or any method in the back end (like debug mode, limit rows), to limit the data amount.

  • If possible, simplify your data model and charts as much as possible, but it should still represent the parts relevant to your problem. This might be a rather critical task if you don't know what could be relevant. So if in doubt, leave it as is.

  • Some people can't open others apps (e.g. due to personal edition, or they just don't run the software at the time reading your post). try to post some / most of the information also in the posting itself.

  • Add what you expect to see and what you actually see, incl. the steps to reproduce.

That's what I think of right now, no guarantee to be complete.

Even if you don't follow all of these steps, it's likely that you get an answer. It might just last a little longer.

Hope this helps,

Stefan

Not applicable
Author

OK, I'm trying to add a sample app here. The data is in the very small Excel file attached to this; I guess you'll have to modify the path in the script to access it.

Very simply, here's what I'm trying to do: We have a new opportunity with this customer, starting June 8, 2012. I want to find the first date the customer was contacted  AFTER June 8, and then identify the last date the customer was contacted, and (if they exist) the second last, and third last dates.

For this data, the Opp_StartDate is 6/8/12, and there are activities on 6/11/12, and 6/29/12. My table correctly shows the last date, 6/29/12, but the field for second last is Null, which is wrong, and for third last is Null as well (which is correct, but I don't know if it's correct because there are only two activities after the Opp_StartDate, or because I'm doing it incorrectly!).

What I want to see in my table is:

ACME    6/8/12   6/11/12     6/29/12   6/11/12  (Null)   PDEV

Any help would be appreciated!

Not applicable
Author

Hi,

In your chart the field for second last is NULL, because there are two lines of data which match this criteria and FirstSortOrder function returns NULL if there are more than one.

Pls try the following:

=firstSortedValue(Distinct OnDate,-OnDate,2)

best regards,

http://quickdevtips.blogspot.com

Not applicable
Author

Thank you, sir! That cleared up the problem for me.