stock

[raadwiptec]

hi All,

This is my scenario. Please suggest. I don't have any specific data so far.it is only initial stage and few trial and error checks are going on . I require this on the script not as an expression.

my data to some extent looks like the below.

basicall I need to calculate the average value. I have used an excel to calculate the average

so the calculation should be something like the below

12 price value remains as same

11 is average(12 and 11)

10 is average (12,11, and 10)

09 is average (12,11,10 and 09)

so on

secondlt the calculation should be for 2014 ,20103 until back. .so when I click on the calendar an year and month  it should reflect the average also.

year of stock	month of stock	price value	average price value
2015	1	3,20000	3,93333
2015	2	2,10000	4,00000
2015	3	4,20000	4,19000
2015	4	2,50000	4,18889
2015	5	4,40000	4,40000
2015	6	2,70000	4,40000
2015	7	4,20000	4,68333
2015	8	7,50000	4,78000
2015	9	7,40000	4,10000
2015	10	4,30000	3,00000
2015	11	2,20000	2,35000
2015	12	2,50000	2,50000

[raadwiptec]

hi kush,

can you provide me for similar one.

1st month -- same price

2nd month - Aveg of 1 and 2

3rd month - Avg of 1 and 2,3

4th month - Avg of 1 and 2,3 and 4

soon until 12th month

[cwolf]

You can solve your problem by means of rolling periods:

//Creating rolling periods

PERIODSt:
LOAD
Autonumber(PERIOD) as ID,
PERIOD
Where Day(PERIOD)=1
;
LOAD
RecNo()+$(vMinDate)-1 as PERIOD
AutoGenerate $(vMaxDate)-$(vMinDate)+1;

PERIODS:
LOAD 
ID as ID.AsOf,
ID + 1 - IterNo() as ID
Resident PERIODSt
while IterNo() <= 12; //<=18 for finacel periods

Right Join (PERIODS)
LOAD
ID,
PERIOD 
Resident PERIODSt;

Right Join (PERIODS)
LOAD
ID as ID.AsOf,
PERIOD as PERI12
Resident PERIODSt;

DROP Fields ID,ID.AsOf;
DROP Table PERIODSt;

// Creating the rolling stock values
STOCK12:
NoConcatenate
LOAD
PERIOD,
PERI12
Where PYEAR=PYEAR12 //rolling periods only within one year
;
LOAD
PERIOD,
PERI12,
Year(PERIOD) as PYEAR,
Year(PERI12) as PYEAR12
Resident PERIODS;

DROP Table PERIODS;

Left Join(STOCK12)
LOAD 
MATNR,
PERIOD,
PRICE
Resident STOCK;

// Joining the average to stock table
Left Join(STOCK)
LOAD
MATNR,
PERI12 as PERIOD,
Round(Avg(PRICE),0.01) as AvgPrice
Resident STOCK12
Group By MATNR,PERI12;

DROP Table STOCK12;

See attachment for a full example with 12 months ("Main 1" in the script) and 18 months ("Main 2" in the script).

[raadwiptec]

hi sunny,

After adding the expression its good. But when I try to sort it descending the values are changing?

[sunny_talwar]

If you want descending order of month, change the expression to this:

=If(Sum([Price Value]) > 0, RangeAvg(Below(Sum({<[Month of Stock] = >}[Price Value]), 0, RowNo())))

[raadwiptec]

Hi Sunny,

Can I send the file to ur email id as my results are getting too much varied time to time..my expression was working but now it does not seem to work....the same with ur script..

[sunny_talwar]

Sure, send me a private message and I will share my email id with you

[raadwiptec]

hi sunny,

where can I send a pvt msg here? I do not find any option...