Oracle Communities

Oracle Analytics and AI

Child Item

Expressions function (Case switch or Case if)

Tony_OAS_Apex_User

I want to populate Amount_2020 at year 2021 with 1.02 ratio.

I tried to use expression case switch or case(if) on Workbook or on analysis.

case when Year =2021 then Amount_2020* 1.02 else Amount_2020 end.

but failing to getting the amount_2020 at 2020 to year 2021 using the case expression..

any idea?

Find more posts tagged with

Accepted answers

Gianni Ceresa

You should look at the AGO function, that one will let you get the Amount for 2020 when on the 2021 year. And then, you can add a CASE WHEN to handle the case of the Amount existing already for that year, and if yes you use it, or the Amount doesn't exist (like for 2021) and in that case you do an AGO(…) * 1.02 .

Something along that line should do the job.

All comments

Gianni Ceresa

What does your source look like? The query behind this table?

Does it retrieve A, B, Year, Amount? Or something else?

My first guess is that in the row-by-row retrieved by the query, when year = 2021, there is nothing in the Amount_2020 column, and therefore your CASE WHEN is doing nothing * 1.02 = nothing.

Tony_OAS_Apex_User

Thats the table structure. yes I am trying to get A, B, Year, Amount only.

yes. there's nothing .. because there's no data on that .. so my question was is there anyway I can get
2020 1000 to 2021 1000x1.02..

Thanks~

Gianni Ceresa

Something along that line should do the job.

Tony_OAS_Apex_User

AGO function?.. ok.let me look at that option!.. thanks!