HANA calculation

[sk_kamaruzzaman]

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

[pfefferf]

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

[sk_kamaruzzaman]

Hi,

Thanks for the reply

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

Regards,

Kamruz

[Former Member]

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