Skip to Content
-2

HANA calculation

Oct 27, 2017 at 09:47 AM

74

avatar image
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)
10 |10000 characters needed characters left characters exceeded

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

0
* Please Login or Register to Answer, Follow or Comment.

2 Answers

avatar image
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
10 |10000 characters needed characters left characters exceeded
avatar image
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
10 |10000 characters needed characters left characters exceeded