nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Complex Calculation - unable to fulfill in OAC RPD Client Admin Tool due to its limited functions

User_SJEVT

Hi Team,

I am having a calculation given by ETL folks, which I am unable to achieve in OAC RPD Client Admin Tool due to its limited functions. Can you pls assist here as how can I fulfil the need in RPD.

Calculation:

Dunning_Level:

CASE WHEN NVL(FLOOR((TRUNC(sysdate) - TRUNC(TERM_DUE_DATE))),-1) < 1 THEN 0
WHEN FLOOR((TRUNC(sysdate) - TRUNC(TERM_DUE_DATE))) >=1 AND FLOOR((TRUNC(sysdate) - TRUNC(TERM_DUE_DATE))) <=7 THEN 1
wHEN FLOOR((TRUNC(sysdate) - TRUNC(TERM_DUE_DATE))) >=8 AND FLOOR((TRUNC(sysdate) - TRUNC(TERM_DUE_DATE))) <=14 THEN 2
WHEN FLOOR((TRUNC(sysdate) - TRUNC(TERM_DUE_DATE))) >=15 THEN 3 end DUNNING_LEVEL

Thanks,

Sohan

Find more posts tagged with

Tips & Tricks

OAC

OAS

Complex Calculation

RPD Admin Client Tool Limitations

Accepted answers

Federico Venturin - Oracle

Hi @User_SJEVT ,

You can easily implement the calculation in Oracle Analytics by using the proper funtions:

Use IFNULL instead of NVL
Use CURRENT_DATE instead of sysdate
Use TIMESTAMPDIFF to calculate the difference between 2 dates (e.g. TIMESTAMPDIFF(SQL_TSI_DAY, CURRENT_DATE, TERM_DUE_DATE)

However, I agree with @KhaderBelgoud-Oracle and I highly recommend you to move this calculation to the database.

All comments

KhaderBelgoud-Oracle

Hi,

Can you please let us know what is the outcome/result of this calculation so that we can understand the cause of the issue. In order to test this calculation, you may specify WHEN clause one by one and check the results.

In order to keep the RPD simplified, It would be a good idea if you can move this calculation to the database

Thanks,

Federico Venturin - Oracle

Hi @User_SJEVT ,

You can easily implement the calculation in Oracle Analytics by using the proper funtions:

Use IFNULL instead of NVL
Use CURRENT_DATE instead of sysdate
Use TIMESTAMPDIFF to calculate the difference between 2 dates (e.g. TIMESTAMPDIFF(SQL_TSI_DAY, CURRENT_DATE, TERM_DUE_DATE)

However, I agree with @KhaderBelgoud-Oracle and I highly recommend you to move this calculation to the database.

Gianni Ceresa

As much as I would agree with the above comments that complex calculations are better performed in the database, in your case it can't work…

You are mostly facing a single expression repeated multiple times: FLOOR((TRUNC(sysdate) - TRUNC(TERM_DUE_DATE)))

By using sysdate, you prevent yourself of being able to calculate this and store the result, because every single day you would need to update that column. If not, then your ETL guys had a very wacky formula and crossed their fingers to never have to reload that data again, or they would corrupt it.

You can simplify the expression to avoid some useless calculations, that will give you a better performance (but the DB optimizer maybe already did that kind of optimization anyway):

Can TERM_DUE_DATE be null? If yes, do you want your value to be 0? (in the original expression there is a very random NVL placed outside a calculation, not sure why if not to try to handle a null TERM_DUE_DATE)

An example of what I mean:

CASE

WHEN NVL(TERM_DUE_DATE) OR FLOOR((TRUNC(sysdate) - TRUNC(TERM_DUE_DATE))) < 1 THEN 0

WHEN FLOOR((TRUNC(sysdate) - TRUNC(TERM_DUE_DATE))) BETWEEN 1 AND 7 THEN 1

WHEN FLOOR((TRUNC(sysdate) - TRUNC(TERM_DUE_DATE))) BETWEEN 8 AND 14 THEN 2

ELSE 3

END

It does the same thing as your original expression, but you can easily see that the FLOOR((TRUNC(sysdate) - TRUNC(TERM_DUE_DATE)))calculation is used a lot less.

In the same way you could write something "longer" apparently but that will calculate that value even less:

CASE

WHEN NVL(TERM_DUE_DATE) OR FLOOR((TRUNC(sysdate) - TRUNC(TERM_DUE_DATE))) < 1 THEN 0

ELSE

CASE FLOOR((TRUNC(sysdate) - TRUNC(TERM_DUE_DATE)))

WHEN  1 THEN 1

WHEN  2 THEN 1

WHEN  3 THEN 1

WHEN  4 THEN 1

WHEN  5 THEN 1

WHEN  6 THEN 1

WHEN  7 THEN 1

WHEN  8 THEN 2

WHEN  9 THEN 2

WHEN 10 THEN 2

WHEN 11 THEN 2

WHEN 12 THEN 2

WHEN 13 THEN 2

WHEN 14 THEN 2

ELSE 3

END

END

Now that you have alternative, "simplified", versions of your expression, you can use the LSQL formulas posted above to translate it into your RPD.

The CASE-WHEN works in the same exact way (in both if-else or switch version). There isn't an equivalent of TRUNC for dates (maybe CAST, but I wouldn't bother…), but TIMESTAMPDIFF should handle that just fine.