Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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,
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.
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,
You could probably use FirstSortedValue() with Aggr() for this. It would help if you could post a sample app.
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.
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,
Here are some ideas that came to my mind regarding creating a sample app.
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
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!
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,
Thank you, sir! That cleared up the problem for me.