SAP HANA calculated column - get last month data

Question

I had created a calculated column to get last month data by subtracting '1' from month using the following code:

string(int(rightstr(leftstr(string(now()),7),2))-1)

But it doesn't work when it's january (january = 1, so 1-1 = 0).

I have already researched about if else statement, but didn't find about it in Column Engine language.

Tried the following code, but doesn't work.

if('ActualMonth'='1', string(int("MesAtual")+11), if("ActualMonth" > '1', 'ActualMonth', string(int('ActualMonth')-1)) )

Can anyone help me?

Thanks in advance.

Answer 1

As provided in https://answers.sap.com/questions/12942257/sap-hana-calculated-column-get-last-month-data.html one way to do this efficiently is using date functions :

component( addmonths(now(), -1) , 2)

This form

1. takes the current server time (now()) ,
2. substracts one month (by adding -1)
3. and extracts the date component 2 (month).

More on this can be found in the reference documentation.