• Replies 4 replies
  • Subscribers 962 subscribers
  • Views 91 views
  • Users 0 members are here
Related

Days between plan statuses formula help

Kelly Cronin
$organization

Hi all,

I am working on a portfolio dashboard for front-line fundraisers, and management would like to see a widget that shows the average time between a proposal/solicitation is submitted and when the gift comes in. 

My thought is to use the days in most recent status with value filters of recent status = Submitted and the current status = Commitment. This way it is only looking at plans that had a solicitation that yielded a successful contribution. At the dashboard level, I have campaign filters that narrow this down to just this year's campaigns.  However, the result I'm getting is 5,380 days or more than 14 years!  

Do y'all have any suggestions for what may be going awry? Or maybe there is a better way to calculate this?

Thank you!

Parents Reply Children
  • $organization in reply to Chris Wallingford

    That certainly helps!  I added that filter at the widget level and it brought the number from 5,380 days (14 years) to 826 days (2.26 years), so definitely a step in the right direction, but it still feels a high.

  • $organization in reply to Kelly Cronin

    Great. Next step Kelly, I think is to either:

    • Remove the AVG() function, then click on Plan Days in Most Recent Status and change the Type to Average
      • ( [Average Plan Days In Most Recent Status] , [Most Recent Plan Status] , [Plan Status] )
    • Click on Plan Days in Most Recent Status and change the Type to All Items, and then change the AVG() function so that is surrounds ONLY Plan Days in Most Recent Plan Status, and then surround the whole thing in parenthesis
      • ( AVG ([Plan Days In Most Recent Status]) , [Most Recent Plan Status] , [Plan Status] )

    These 2 resulting formulas are the same, it's just that one is showing the AVG() function explicitly while the other has it baked into the "Type" selected for the value as [Average whateverthevaluefieldis].

    Chris

  • $organization in reply to Chris Wallingford

    Thank you, Chris!  That brought the number down to ~28 days which seems MUCH more reasonable. I was able to double-check using a pivot and the value matched.