locked
Finding accounts with opportunities actual close date last year, but no actual close date this year RRS feed

  • Question

  • Hi all,

    I need a solution for this problem -

    It could be nice, if there in advanced find, is a "not this year", not x month etc -

    I know i can do this task in Marketing Lists, where I can remove account that have actual closing date this year...

    But I need to do it as an advanced find, as this way is how i can get it show dynamical in dashboards - make dynamic excel etc.

    How would you come about this problem?

    Friday, April 15, 2016 7:10 AM

All replies

  • Hi,

    If you want opportunities with actual close date last year you set up an advanced find where actual close date on or after 2015-01-01 AND actual closedate on or before 2015-12-31. Or did I miss something in your question?

    Regards


    Rickard Norström Developer CRM-Konsulterna
    http://www.crmkonsulterna.se
    Swedish Dynamics CRM Forum: http://www.crmforum.se
    My Blog: http://rickardnorstrom.blogspot.se

    Friday, April 15, 2016 7:24 AM
  • I am trying to find ACCOUNTS with opportunities with actual close date before 2015-12-31

    If I perform your suggestion, i suppose it will return accounts with opportunities which are closed later then this date, but i will also include accounts that have an "younger" close data, for example this year?

    So i need to exempt those accounts that have an opportunity in 2016

    Hope this makes sense - English isn't my first language:)

    Friday, April 15, 2016 7:31 AM
  • No, the AND-clause will eliminate those that have close date this year since both the searches must return true. If you've studied math it would be something like 2<x<5 wich will only give x that are larger than 2 and smaller than 5. If you find the opportunity you will find the account as well. You can add the parent account field to the presentation, you can also add fields from that account to the result view. Will that be enough for your need or is there something else you're looking for. (English isn't my first language either so no sweat :))

    Regards


    Rickard Norström Developer CRM-Konsulterna
    http://www.crmkonsulterna.se
    Swedish Dynamics CRM Forum: http://www.crmforum.se
    My Blog: http://rickardnorstrom.blogspot.se

    Friday, April 15, 2016 8:37 AM
  • Sorry, i "miswrote". the sentence "but i will also include...." should have said that I want to exclude accounts with a close date this year -

    The end result should be -

    Accounts with opportunity with latest "actual closing date" older then 2015-12-31 - but with no "open" or "actual closing date" this year (2016)

    End result will show the progress, as we aspire to have opportunities on all accounts within this year.

    Friday, April 15, 2016 10:13 AM
  • Ok, so what you are looking for are account that you have had business contacts with last year but not this year? That is tricker, I doubt you can get that from fetch, and the SQL query for that isn't trivial either. I suppose you would need to do a report on that and you will need to be on prem since you want to use SQL.

    Sorry, to my knowledge I don't think you can do that in advanced find.

    Regards


    Rickard Norström Developer CRM-Konsulterna
    http://www.crmkonsulterna.se
    Swedish Dynamics CRM Forum: http://www.crmforum.se
    My Blog: http://rickardnorstrom.blogspot.se

    Friday, April 15, 2016 10:28 AM
  • yeah, I was fearing there was no easy solution.

    Firstly i tried deploying this, http://blog.cobalt.net/blog/building-not-in-queries-using-dynamics-crm-advanced-find

    but sadly i misunderstood the intend of this fix.

    I was hoping for a solution like the above, which could add a "not" in front off the different search perimeters in advanced find.

    I find it odd that i does not exist.

    Anyways, thanks a lot for your help

    /Jan Olsen

    Friday, April 15, 2016 10:33 AM
  • The problem is that you really don't just want a "not" in front of stuff, you're trying to do something like

    SELECT name FROM account WHERE COUNT(SELCT * FROM opportunity WHERE actualend > '2015-01-01' AND actualend < '2015-12-31') >0 AND COUNT(SELECT * FROM opportunity WHERE actualend > '2016-01-01') == 0

    (very much not working SQL I would imagine but...) what that does is finding accounts that have one or more opportunity last year but none this year and you can't set up that sort of query in fetchXML AFAIK.

    Regards


    Rickard Norström Developer CRM-Konsulterna
    http://www.crmkonsulterna.se
    Swedish Dynamics CRM Forum: http://www.crmforum.se
    My Blog: http://rickardnorstrom.blogspot.se

    Friday, April 15, 2016 10:44 AM