Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Spread Expenses by day

Received Response
21
Views
6
Comments
Pierre Delareux
Pierre Delareux Rank 3 - Community Apprentice
in Oracle Analytics Cloud and Server

Summary

Spread Expenses by day

Content

Hi,

How can I get the expenses by day in a BIP Data Set ?

I would like to have the expenses of my projet from the data date to the end of the project.

Thanks

Regards,

Pierre

Answers

  • Pierre Delareux
    Pierre Delareux Rank 3 - Community Apprentice

    I have to adapt the Start date and End Date to the P6 query no ?

    What about <date: 1 day before project start date> ? does it remain like this ?

    Pierre

  • Pierre Delareux
    Pierre Delareux Rank 3 - Community Apprentice

    hi,,

    Thanks for this answer.

    Which query do I have to use in your exemple ?

    Pierre

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    Do it in the/a query ...

    this will give you the dates for your project:

    SELECT LEVEL n,

            <date: 1 day before project start date> + level AS dt

          FROM DUAL

            CONNECT BY LEVEL <= (project end date - project start date)

    Then you can add in the daily average:

    Total expenses / number of days and put that on each row in the above date list ...

    with dts as (

    SELECT LEVEL n,

            <date: 1 day before project start date> + level AS dt

          FROM DUAL

            CONNECT BY LEVEL <= (project end date - project start date)),

    num_days as ( select max(n) dys from dts)

    select x.*,

    (tot_exp/d) daily_avg_exp

    from (

    select cal.*, (select dys from numb_days) d,

    (select total_exp from project_table where project _id = <proj_id>) tot_exp

    from dts cal) x

    Let the Database handle the data ...

  • Pierre Delareux
    Pierre Delareux Rank 3 - Community Apprentice

    If I understand well you split the global cost of the project linearly between the start and the end date.

    I would like to increase the accuracy by getting the planned cost per day.

    Pierre

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    replace the these bits with what you have ...

    with dts as (

    SELECT LEVEL n,

            <date: 1 day before project start date> + level AS dt

          FROM DUAL

            CONNECT BY LEVEL <= (<project end date> - <project start date>)),

    num_days as ( select max(n) dys from dts)

    select x.*,

    (tot_exp/d) daily_avg_exp

    from (

    select cal.*, (select dys from num_days) d,

    (select total_exp from project_table where project _id = <proj_id>) tot_exp

    from dts cal) x

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    "How can I get the expenses by day in a BIP Data Set ?

    I would like to have the expenses of my projet from the data date to the end of the project."

    I gave you what you asked ... I have no idea what source you have - how the data is laid out, etc ...