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)