9 Replies Latest reply: Jul 6, 2012 11:11 AM by Kevin Bertsch RSS

Find first activity after given date..

Kevin Bertsch

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

  • Re: Find first activity after given date..
    Kevin Bertsch

    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.

  • Re: Find first activity after given date..
    Jason Michaelides

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

    • Re: Find first activity after given date..
      Kevin Bertsch

      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.

      • Re: Find first activity after given date..
        B Aydin

        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,

      • Re: Find first activity after given date..
        swuehl

        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

        • Re: Find first activity after given date..
          Kevin Bertsch

          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!