-2

# HANA calculation

Oct 27, 2017 at 09:47 AM

74

Former Member

previous-data.png

Hi All,

I want to populate new column based of sales different / day difference.

8/8/2016 - 7/30/2017 = 356 day

sales different in two year.

Day difference between two dates.

new field = sales different in two year / Day difference between two dates *365

Regards,

Kamruz

previous-data.png (11.3 kB)

Please can you add some more details to your question instead of just posting your spec dump?

How you wanna do that (e.g. with an SQL Script, via an XS Classic/XS Advanced application ...)?

What have you tried so far, what issues you get?

...

Regards,
Florian

Former Member Oct 27, 2017 at 06:06 PM
0

Hi Kamruz,

In order to solve your problem you need 2 generated column in your table:

1. finding the prior record date

2. Finding the prior record Sales amount

In order to solved this, you can use a SQL script to find the corresponding data:

```select
a.*,
b."SDATE" as PDate,
b."sales" as PSales
from (select
"SNO",
"SDATE",
"SYEAR",
"sales" ,
row_number() over (partition by "SNO"
order by "SDATE" asc) as scounter
from "SAPBODS"."test_table") a
left outer join (select
"SNO",
"SDATE",
"SYEAR",
"sales",
(scounter)+1 as pcounter
from ( select
"SNO",
"SDATE",
"SYEAR",
"sales" ,
row_number() over (partition by "SNO"
order by "SDATE" asc) as scounter
from "SAPBODS"."test_table")
--where scounter > 1
) b on a."SNO" = b."SNO"
and a.scounter = b.pcounter;
```

This would sort the data in the ascending order and provide you the previous Data and Sales amount in the same row, making it easier for calculation.

You can then build a HANA model to perform the division.

thanks,

Kumar Attangudi Perichiappan

Share
Former Member Nov 13, 2017 at 07:03 AM
0

Hi,

Thanks for the reply

I used Window Function LEAD and got solved. Closing this thread.

Regards,

Kamruz

Share