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?
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,
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!
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: