Reporting and Analytics for ERP

Get Involved. Join the Conversation.

Topic

    Doron de Jong
    How to do an average days to approve formula?
    Topic posted February 19, 2018 by Doron de JongRed Ribbon: 250+ Points, tagged BI Publisher, Fusion Financial reporting, Fusion Procurement reporting, OBI Answers, OBIEE Answers, OTBI, Reports, Tip 
    148 Views, 1 Comment
    Title:
    How to do an average days to approve formula?
    Content:

    Hi all,

     

    I am trying to do an average days formula from a requisition submission date and when it was approved, and then put another column in with the month it was approved in so I can create a trend analysis, I am struggling to create the average formula. I need to use this formula for many other OTBI dashboards (date of submitting expenses to approval for example). Any ideas please?

     

    My Excel dashboard that I use macros from OTBI for which I am trying to replicate is below, as are my subject areas.

     

    Thanks

    Document:
    Doc2.docx (644KB)

    Comment

     

    • Aaron Leggett

      Hi Doron, 

      Will the number of days between Submission date and Approval date be beneficial to you at all, to work out any averages? I have created similar logic but for an Aged Creditors report. 

      TimestampDiff(SQL_TSI_DAY, SubmissionDate, ApprovalDate)

      Use the above formula on a column and it will give you the number of days between the two dates (use the correct OTBI SQL for those two columns of course). 

      To determine what month it was approved in, bring in another Approval Date Column. Edit the formula to: MONTH("ApprovalDate"). This will bring back the Number of the Month. To get the name, go into Column Properties > Data Format. Check the override text box. From the dropdown, select Month Name or Month Name Abbreviated (whichever one you prefer). 

      Let me know if that works. I will give it a go if you are stuck, just let me know what subject area you are using.

      Kind Regards

      Aaron