Moving average calculation in hana

[Former Member]

hello guys

we have a requirement where we have to calculate moving average for value based on x last days

we try to look all the options in hana studio to do this calculation.. as the product is new finding lot of challanges

if anyone how to do this calculation in HANA using formulae or SQL Script

or what ever please let me know, your help will be much appreciated

thanks

ganesh

[Former Member]

HI DIVYA PRAKASH,

Here is my Scenario,

we got CALDAY AND QUANTITY fields, we have to calculate moving average for the last 5 days on quantity

example

cal day quantity Moving avg

31/01/2011 10 4.4

29/01/2011 5 4.4

28/01/2011 1 7.4

27/01/2011 1 7.2

26/01/2011 5 7

25/01/2011 10 6

24/01/2011 20 4

it was 5 day cycle, the moving avg need to be sum of the qunaity for the last days divide by 5

hope you undertand

thakns

ganesh

[former_member234877]

Hey,

Lets assume that my table has the structure below:

DAILY_INTENTORY_ID CALENDAR_DAY QUANTITY

1 2011/01/01 10

2 2011/01/02 20....

The following SQL statement gives you what you want ( I will re-write this using SQLScript and share it here later)

select

t3.DAILY_INVENTORY_ID,

t3.CALENDAR_DAY,

t3.QUANTITY,

sum(t3.QUANTITY_TO_BE_SUM) LAST_FIVE_TOTAL,

sum(t3.QUANTITY_TO_BE_SUM)/5 AVRG_OF_LAST_FIVE

from (

select t1.DAILY_INVENTORY_ID, t1.CALENDAR_DAY, t1. QUANTITY,

t2.calendar_day calendar_day_2, t2.QUANTITY QUANTITY_TO_BE_SUM

from "FACT_DAILY_INVENTORY" t1, "FACT_DAILY_INVENTORY" t2

where t1.calendar_day >= t2.calendar_day

and days_between(t2.calendar_day, t1.calendar_day) < 5

order by 1,4

) t3

group by t3.DAILY_INVENTORY_ID, t3.CALENDAR_DAY, t3.QUANTITY

order by 1

Edited by: Zafer Yumak on Oct 6, 2011 12:08 AM

[Former Member]

Moving Average thru graphical is tricky. I can trying helping with SQL. Please share your scenario.