Forum Stats

  • 3,728,534 Users
  • 2,245,647 Discussions
  • 7,853,586 Comments

Discussions

Waterfall Bridge Chart

hiddenonearth
hiddenonearth Member Posts: 91 Red Ribbon

Hello,

I am trying to create a waterfall charts in Oracle Apex 20.1. I followed the recommendation to post my problem statement also in this forum since the language I am using is SQL respectively PL/SQL.

In my chart I want to display a bar chart for every month and the deltas for each month. Furthermore, the deltas of each month should only affect the corrsponding month (bar in chart). For instance, the deltas for the month april only are applied for this month. For may the computation starts from the beginning.

I hope the following explanation helps more to tackle the problem:

  • In my application I have one modal dialog page where everytime a user can define a new project with the delta value (i.e., something like project value increaes or decreases) with a due date. One Project can have multiple values based on its progress. Meaning the number of deltas is unknown and thus should be dynamic in the chart
  • Based on the ascending due dates per month the delta progress should be computed for every month. Every delta should be a new starting proint for further computation within the month range
  • The month and its deltas are not connected and thus the computation should not be ongoing
  • The value for the months is being given afterwards and is applied for every delta within the month. So April with the value 200 could have 5 deltas, while May with the value 100 could have only two for instance

With help the following chart is being displayed with one delta and ongoing computation:

with PLAN_PROJECT_MONTHLY as (select to_char(PLAN_DATE, 'MM / YYYY') mon, 0 lo, SUM(PLAN_DELTA) hi, 0 dir, '#92A2BD' clr from PLAN_PROJECT group by to_char(PLAN_DATE, 'MM / YYYY'))
select mon, lo, hi, dir, clr from (
select row_number() over(order by to_date(mon,'MM / YYYY')) rn, mon, lo, hi, dir, clr from PLAN_PROJECT_MONTHLY union all
 select rn, mon, lo, hi, dir, clr from(
 select row_number() over(order by to_date(mon,'MM / YYYY')) rn,
  'Delta '||row_number() over(order by to_date(mon,'MM / YYYY')) mon,
  least( hi, lead(hi,1) over(order by to_date(mon,'MM / YYYY'))) lo,
  greatest( hi, lead(hi,1) over(order by to_date(mon,'MM / YYYY'))) hi, 
  sign(lead(hi,1) over(order by to_date(mon,'MM / YYYY'))- hi ) dir,
    case when sign(lead(hi,1) over(order by to_date(mon,'MM / YYYY'))- hi ) = -1 then '#d20000' 
     else case when sign(lead(hi,1) over(order by to_date(mon,'MM / YYYY'))- hi ) = 1 then '#3db014' 
     else case when sign(lead(hi,1) over(order by to_date(mon,'MM / YYYY'))- hi ) = 0 then '#92A2BD'
     end end end
     clr 
 from PLAN_PROJECT_MONTHLY
 ) where lo is not null
) order by rn, instr(mon,'Delta')

The corresponding chart looks like this:

Here is what the final chart should look like:

Please note that the number of deltas for each month are not fixed and are dynamically selected by each user.

Any help is appreciated to get a workaround.

Thank you.

Kind regards

P.S. You may find the current woraround in my Oracle Apex Workspace

  • Workspace name: WS_2020
  • User: ORACLE_TECH
  • PW: TechCasesEnv
  • App: 112425 (Oracle Waterfall Delta Chart with Reference Lines)
Tagged:

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,322 Red Diamond

    Hi, @hiddenonearth

    Whenever you have a question, please post a little sample data in a usable form (such as CREATE TABLE and INSERT statements), so the people who want to help you car re-create the problem and test their ideas. Also post the exact results you want from the given data, and an explanation (in general terms) of you get those results from that data. (In this case, the results will be the result set that you pass to your graphics software.) Post everything right in this space. If the data is already somewhere else, that's great: it will be easy for you to copy it here.

    Always say which version of Oracle you're using (e.g. 12.2.0.1.0).

    See: How to Ask Questions in Developer Community Spaces - oracle-tech

    order by to_date(mon,'MM / YYYY')
    

    Means that '01 / 2021' comes before '12 / 2020'. If mon is a DATE, then you should ORDER BY TRUNC (mon, 'MONTH'), or, if it's already truncated, ORDER BY mon. You can always display it in 'MM / YYYY' format, or ay format you want.


     case when sign(lead(hi,1) over(order by to_date(mon,'MM / YYYY'))- hi ) = -1 then '#d20000' 

       else case when sign(lead(hi,1) over(order by to_date(mon,'MM / YYYY'))- hi ) = 1 then '#3db014' 

       else case when sign(lead(hi,1) over(order by to_date(mon,'MM / YYYY'))- hi ) = 0 then '#92A2BD'

       end end end

    Nested CASE expressions are seldom needed and always confusing. Instead of the nested expression above, you caan say:

    case sign (lead (hi, 1) over (order by to_date(mon,'MM / YYYY')) - hi )
      when -1 then '#d20000' 
      when  1 then '#3db014' 
      when  0 then '#92A2BD'
    end
    


  • hiddenonearth
    hiddenonearth Member Posts: 91 Red Ribbon

    Hi @Frank Kulash,

    thanks for your response and first helpful insights. Since some of you may not familiar with the Oracle APEX environment I will add some sample code for better understanding. The visualization then will be based on the query.

    with PLAN_PROJECT (mon, val, delta_one, delta_two, delta_three, dir as (
     select 'Month 1' mon, 150 val, 30 delta_one, -15 delta_two, 0 delta_three, 0 dir from dual union all
     select 'Month 2' mon, 100 val, -20 delta_one, 45 delta_two, 0 delta_three, 0 dir from dual union all
     select 'Month 3' mon, 120 val, 10 delta_one, 20 delta_two, -5 delta_three, 0 dir from dual
    )
    select mon, val, delta_one, delta_two, delta_three, dir from PLAN_PROJECT union all
     select * from(
      select 'Delta '||row_number() over(order by mon),
          least( val, lead(val,1) over(order by mon)) lo,
          greatest( val, lead(val,1) over(order by mon)) hi, 
          sign(lead(val,1) over(order by mon)- val ) dir
    case sign (lead (hi, 1) over (order by to_date(mon,'MM / YYYY')) - hi )
      when -1 then '#d20000' 
      when  1 then '#3db014' 
      when  0 then '#92A2BD'
    end
      from PLAN_PROJECT
     ) where val is not null
    )
    order by mon desc
    

    If something is not clear or wrong, feel free to correct the sample query. Please not that in my table the delta values are listed all in one column and are not seperatd in delta_one, delta_two, etc.

    As explained above my desired solution is to build the waterfall chart like this after running a successful query (picture two):

    For further information you may have a look the the sample application .The credentials are listed above.

    Please let me know if you need further information.

    Thanks.

    P.S. I am using Oracle SQL Developer 19.1.0

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,322 Red Diamond

    Hi,

    So, given the plan_project_table you posted, you want these results:

          DELTA_   DELTA_  DELTA_
            ONE_     TWO_  THREE_
      VAL CHANGE   CHANGE  CHANGE
    ------- ------ ------ ------
      150     180     165    165
      100      80     125    125
      120     130     150    145
    

    Is that right? If so, here's one way:

    SELECT   val
    ,	 val + delta_one				AS delta_one_change
    ,	 val + delta_one + delta_two			AS delta_two_change
    ,	 val + delta_one + delta_two + delta_three	AS delta_three_change
    FROM	 plan_project
    WHERE	 val IS NOT NULL
    ;
    

    If what happens when val is NULL is important, then include some examples where val is NULL in the sample data.

    You don't need CASE expressions, or the mon or dir columns for the output, but if the order of rows is significant you may need them in an ORDER BY clause.

  • hiddenonearth
    hiddenonearth Member Posts: 91 Red Ribbon

    Thanks for your response and help. In my table the delta values are all stored in on column with another column for the corresponding project. Moreover, for each delta I have a due date for the ordering and computation. The computation should be done ascending order for each month starting the val column.

    val + delta_one + delta_two + ... 
    

    unfortunately cannot be applied for my specific case.

    I hope this makes the sample query a bit more clearer.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,322 Red Diamond

    Hi, @hiddenonearth

    I hope this makes the sample query a bit more clearer.

    No, not at all.

    The plan_project table you posted has 6 columns: mon, val, delta_one, delta_two, delta_three and dir. None of those columns are DATEs, and there is no due_date column.

    Are you starting with a table that looks like the plan_project "table" you posted? If not, post CREATE TABLE and INSERT statements for a little sample data for a tables that do resemble your actual tables (relevant columns only).

  • hiddenonearth
    hiddenonearth Member Posts: 91 Red Ribbon
    CREATE TABLE PLAN_PROJECT(
        PROJECT VARCHAR(200),
        RESULT   NUMBER,
        DELTA   NUMBER,
     DUE_DATE   DATE
    );
    
    INSERT ALL
      INTO PLAN_PROJECT (PROJECT, RESULT, DELTA, DUE_DATE) VALUES ('First Project', 150, 30, '01/04/2021' )
      INTO PLAN_PROJECT (PROJECT, RESULT, DELTA, DUE_DATE) VALUES ('First Project', 150, -15, '05/04/2021')
      INTO PLAN_PROJECT (PROJECT, RESULT, DELTA, DUE_DATE) VALUES ('First Project', 150, 0,  '11/04/2021')
      INTO PLAN_PROJECT (PROJECT, RESULT, DELTA, DUE_DATE) VALUES ('Second Project', 100, -20, '03/05/2021')
      INTO PLAN_PROJECT (PROJECT, RESULT, DELTA, DUE_DATE) VALUES ('Second Project', 100, 45, '10/05/2021')
      INTO PLAN_PROJECT (PROJECT, RESULT, DELTA, DUE_DATE) VALUES ('Third Project', 120, 10, '05/06/2021')
      INTO PLAN_PROJECT (PROJECT, RESULT, DELTA, DUE_DATE) VALUES ('Third Project', 120, 20, '15/06/2021')
      INTO PLAN_PROJECT (PROJECT, RESULT, DELTA, DUE_DATE) VALUES ('Third Project', 120, 20, '18/06/2021')
    

    This sample query resembles my actual data in the table. Note that the RESULT Column always holds the same value for a month. The delta determines the projects progress (i.e., increase or decrease) in an ascending order for each month.

    Every month should computed the project progress from the beginning for each due date in a cumulative way (but also the possibility for subtraction a delta) and should stop when a new month starts. For every new month the computation should start from its given Result.

    Sample output:

         
      RESULT DUE_DATE 1  DUE_DATE 2 DUE_DATE 3 
    ------- ------ ------ ------ ------
      150     180        165        165
      100      80        125        125
      120     130        150        145
    

    Kind regards.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,322 Red Diamond

    Hi,

    CREATE TABLE PLAN_PROJECT(
        PROJECT VARCHAR(200),
        RESULT   NUMBER,
        DELTA   NUMBER,
     DUE_DATE   DATE
    );
    
    INSERT ALL
      INTO PLAN_PROJECT (PROJECT, RESULT, DELTA, DUE_DATE) VALUES ('First Project', 150, 30, '01/04/2021' )
    

    Don't try to insert VARCHAR2 values, such as '01/04/2021', into DATE columns. It may work sometimes, aand they may even produce the results you want, but why take a chance? Use TO_DATE or DATE literals instead; they work correctly all the time.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,322 Red Diamond

    Hi,

    CREATE TABLE PLAN_PROJECT(
        PROJECT VARCHAR(200),
        RESULT   NUMBER,
        DELTA   NUMBER,
     DUE_DATE   DATE
    );
    
    INSERT ALL
      INTO PLAN_PROJECT (PROJECT, RESULT, DELTA, DUE_DATE) VALUES ('First Project', 150, 30, '01/04/2021' )
    

    Don't try to insert VARCHAR2 values, such as '01/04/2021', into DATE columns. It may work sometimes, and they may even produce the results you want, but why take a chance? Use TO_DATE or DATE literals instead; they work correctly all the time.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,322 Red Diamond

    Hi, @hiddenonearth

    So, if a project has only two rows, you want the third delta to be 0; is that right? I assume you want something similar if aa project has only one row, or has NULL as the delta. Here's one way to do that:

    WITH  data_to_pivot  AS
    (
      SELECT project, result, delta
      ,    ROW_NUMBER () OVER ( PARTITION BY project
      	  	    	     ORDER BY   due_date
    			    )     AS rn
      FROM  plan_project
    )
    SELECT  result
    ,	 result + NVL (d1, 0)					AS du_date_1
    ,	 result + NVL (d1, 0) + NVL (d2, 0)			AS due_date_2
    ,	 result + NVL (d1, 0) + NVL (d2, 0) + NVL (d3, 0)	AS due_date_3
    FROM	 data_to_pivot
    PIVOT	 ( MIN (delta)
    	 FOR rn IN ( 1 AS d1
    	    	    , 2 AS d2
    		    , 3 AS d3
    		    )
    	 )
    ORDER BY result	-- or whatever you want
    ;
    

    If the combination (project, due_date) is not unique, add a tie-breaking expression to the analytic ORDER BY clause.

Sign In or Register to comment.