Skip to Content
avatar image
-2
Former Member

HANA calculation

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)
Add comment
10|10000 characters needed 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

  • Get RSS Feed

2 Answers

  • avatar image
    Former Member
    Oct 27, 2017 at 06:06 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Nov 13, 2017 at 07:03 AM

    Hi,

    Thanks for the reply

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

    Regards,

    Kamruz

    Add comment
    10|10000 characters needed characters exceeded