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

[sunny_talwar]

There might be a easier way, but may be this:

Table:

LOAD [year of stock],

     [month of stock],

     [price value],

     [average price value]

FROM

Community_190287.xlsx

(ooxml, embedded labels, table is Sheet1);

NewTable:

NoConcatenate

LOAD [year of stock] as [Year of Stock],

     [month of stock] as [Month of Stock],

     [price value] as [Price Value],

     [average price value] as [Average Price Value],

     If(Peek('Year of Stock', -11) = [year of stock],

     RangeAvg([price value], Peek('Price Value', -1), Peek('Price Value', -2), Peek('Price Value', -3), Peek('Price Value', -4),

      Peek('Price Value', -5), Peek('Price Value', -6), Peek('Price Value', -7), Peek('Price Value', -8),

      Peek('Price Value', -9), Peek('Price Value', -10), Peek('Price Value', -11)),

     If(Peek('Year of Stock', -10) = [year of stock],

     RangeAvg([price value], Peek('Price Value', -1), Peek('Price Value', -2), Peek('Price Value', -3), Peek('Price Value', -4),

      Peek('Price Value', -5), Peek('Price Value', -6), Peek('Price Value', -7), Peek('Price Value', -8),

      Peek('Price Value', -9), Peek('Price Value', -10)),

     If(Peek('Year of Stock', -9) = [year of stock],

     RangeAvg([price value], Peek('Price Value', -1), Peek('Price Value', -2), Peek('Price Value', -3), Peek('Price Value', -4),

      Peek('Price Value', -5), Peek('Price Value', -6), Peek('Price Value', -7), Peek('Price Value', -8),

      Peek('Price Value', -9)),

     If(Peek('Year of Stock', -8) = [year of stock],

     RangeAvg([price value], Peek('Price Value', -1), Peek('Price Value', -2), Peek('Price Value', -3), Peek('Price Value', -4),

      Peek('Price Value', -5), Peek('Price Value', -6), Peek('Price Value', -7), Peek('Price Value', -8)),

     If(Peek('Year of Stock', -7) = [year of stock],

     RangeAvg([price value], Peek('Price Value', -1), Peek('Price Value', -2), Peek('Price Value', -3), Peek('Price Value', -4),

      Peek('Price Value', -5), Peek('Price Value', -6), Peek('Price Value', -7)),

     If(Peek('Year of Stock', -6) = [year of stock],

     RangeAvg([price value], Peek('Price Value', -1), Peek('Price Value', -2), Peek('Price Value', -3), Peek('Price Value', -4),

      Peek('Price Value', -5), Peek('Price Value', -6)),

     If(Peek('Year of Stock', -5) = [year of stock],

     RangeAvg([price value], Peek('Price Value', -1), Peek('Price Value', -2), Peek('Price Value', -3), Peek('Price Value', -4),

      Peek('Price Value', -5)),

     If(Peek('Year of Stock', -4) = [year of stock],

     RangeAvg([price value], Peek('Price Value', -1), Peek('Price Value', -2), Peek('Price Value', -3), Peek('Price Value', -4)),

     If(Peek('Year of Stock', -3) = [year of stock],

     RangeAvg([price value], Peek('Price Value', -1), Peek('Price Value', -2), Peek('Price Value', -3)),

     If(Peek('Year of Stock', -2) = [year of stock],

     RangeAvg([price value], Peek('Price Value', -1), Peek('Price Value', -2)),

     If(Peek('Year of Stock', -1) = [year of stock],

     RangeAvg([price value], Peek('Price Value', -1)), [price value]))))))))))) as [Average Price Value1]

Resident Table

Order By [year of stock], [month of stock] desc;

DROP Table Table;

[Kushal_Chawda]

not as good as sunny's solution but this is another way of doing it

Data:

LOAD * Inline [

year of stock, month of stock,Price

2015, 1, 320000

2015, 2, 210000

2015, 3, 420000

2015, 4, 250000

2015, 5, 440000

2015, 6, 270000

2015, 7, 420000

2015, 8, 750000

2015, 9, 740000

2015, 10, 430000

2015, 11, 220000

2015, 12, 250000

2014, 1, 320000

2014, 2, 210000

2014, 3, 420000

2014, 4, 250000

2014, 5, 440000

2014, 6, 270000

2014, 7, 420000

2014, 8, 750000

2014, 9, 740000

2014, 10, 430000

2014, 11, 220000

2014, 12, 250000 ];

Final:

LOAD Distinct [year of stock],

avg(Price) as AvgPrice,

1 as [month of stock]

Resident Data

Group by [year of stock];

LOAD Distinct [year of stock],

avg(Price) as AvgPrice,

2 as [month of stock]

Resident Data

where [month of stock]>=2

Group by [year of stock];

LOAD Distinct [year of stock],

avg(Price) as AvgPrice,

3 as [month of stock]

Resident Data

where [month of stock]>=3

Group by [year of stock];

LOAD Distinct [year of stock],

avg(Price) as AvgPrice,

4 as [month of stock]

Resident Data

where [month of stock]>=4

Group by [year of stock];

LOAD Distinct [year of stock],

avg(Price) as AvgPrice,

5 as [month of stock]

Resident Data

where [month of stock]>=5

Group by [year of stock];

LOAD Distinct [year of stock],

avg(Price) as AvgPrice,

6 as [month of stock]

Resident Data

where [month of stock]>=6

Group by [year of stock];

LOAD Distinct [year of stock],

avg(Price) as AvgPrice,

7 as [month of stock]

Resident Data

where [month of stock]>=7

Group by [year of stock];

LOAD Distinct [year of stock],

avg(Price) as AvgPrice,

8 as [month of stock]

Resident Data

where [month of stock]>=8

Group by [year of stock];

LOAD Distinct [year of stock],

avg(Price) as AvgPrice,

9 as [month of stock]

Resident Data

where [month of stock]>=9

Group by [year of stock];

LOAD Distinct [year of stock],

avg(Price) as AvgPrice,

10 as [month of stock]

Resident Data

where [month of stock]>=10

Group by [year of stock];

LOAD Distinct [year of stock],

avg(Price) as AvgPrice,

11 as [month of stock]

Resident Data

where [month of stock]>=11

Group by [year of stock];

LOAD Distinct [year of stock],

sum(Price) as AvgPrice,

12 as [month of stock]

Resident Data

where [month of stock]=12

Group by [year of stock];

Left Join(Final)

LOAD Distinct [year of stock],

[month of stock],

Price

Resident Data;

DROP Table Data;

[raadwiptec]

hi sunny,

Iam still testing,

Can you just explain me how this works for example taking one or 2 months in picture

If(Peek('Year of Stock', -2) = [year of stock],

     RangeAvg([price value], Peek('Price Value', -1), Peek('Price Value', -2)),

[sunny_talwar]

If(Peek('Year of Stock', -2) = [year of stock],

     RangeAvg([price value], Peek('Price Value', -1), Peek('Price Value', -2)),

In simple words the above is checking if hopping back two period has the same year as current row's year then RangeAvg(CurrentRow'sPrice, PreviousRow'sPrice, and Previous to PreviousRow'sPrice)

Important thing here is to have a sorting order Order By [year of stock], [month of stock] desc;

If we don't sort the table correctly, you will see undesirable results.

HTH

Best,

Sunny

[raadwiptec]

hi sunny,

what is hopping back 2 period. can u specify an example

[sunny_talwar]

Here is an example:

year of stock	month of stock	price value	average price value	Peek('Year of Stock', -2)	Avg
2015	1	3,20000	3,93333	-
2015	2	2,10000	4,00000	-
2015	3	4,20000	4,19000	2015	320000, 210000,420000
2015	4	2,50000	4,18889	2015	210000,420000,250000
2015	5	4,40000	4,40000	2015	and so on
2015	6	2,70000	4,40000	2015
2015	7	4,20000	4,68333	2015
2015	8	7,50000	4,78000	2015
2015	9	7,40000	4,10000	2015
2015	10	4,30000	3,00000	2015
2015	11	2,20000	2,35000	2015
2015	12	2,50000	2,50000	2015

But the way the if statement is structured, it will check backwards if the first statement is met, it will never go to the next level

So When this is met ->

If(Peek('Year of Stock', -11) = [year of stock],

    RangeAvg([price value], Peek('Price Value', -1), Peek('Price Value', -2), Peek('Price Value', -3), Peek('Price Value', -4),

      Peek('Price Value', -5), Peek('Price Value', -6), Peek('Price Value', -7), Peek('Price Value', -8),

      Peek('Price Value', -9), Peek('Price Value', -10), Peek('Price Value', -11))

Only for the last row above, you get the average for 12 rows. Next row won't meet this -> Peek('Year of Stock', -11) = [year of stock] and will look for next possible match and will meet the condition here -> Peek('Year of Stock', -10) = [year of stock] and so on..

Does that make sense?

[Anonymous]

Maybe this solution will help:

Sampledata:

LOAD

  Num([year of stock]) as [year of stock],

  Num([month of stock]) as [month of stock],

  Num([price value]) as [price value]

;

LOAD * inline

[

  "year of stock", "month of stock", "price value"

  "2015", "1", "3,20000"

  "2015", "2", "2,10000"

  "2015", "3", "4,20000"

  "2015", "4", "2,50000"

  "2015", "5", "4,40000"

  "2015", "6", "2,70000"

  "2015", "7", "4,20000"

  "2015", "8", "7,50000"

  "2015", "9", "7,40000"

  "2015", "10", "4,30000"

  "2015", "11", "2,20000"

  "2015", "12", "2,50000"

];

MapStock:

Mapping Load

  [year of stock] & '-' & [month of stock] as value,

  [price value] as value

Resident Sampledata;

Averages:

LOAD

  [year of stock],

  [month of stock],

  [price value],

  (alt([price month 1], 0) + alt([price month 2], 0) + alt([price month 3], 0) + alt([price month 4], 0)

  + alt([price month 5], 0) + alt([price month 6], 0) + alt([price month 7], 0) + alt([price month 8], 0)

  + alt([price month 9], 0) + alt([price month 10], 0) + alt([price month 11], 0) + alt([price month 12], 0))

  / (if(IsNull([price month 1]), 0, 1) + if(IsNull([price month 2]), 0, 1) + if(IsNull([price month 3]), 0, 1)

  + if(IsNull([price month 4]), 0, 1) + if(IsNull([price month 5]), 0, 1) + if(IsNull([price month 6]), 0, 1)

  + if(IsNull([price month 7]), 0, 1) + if(IsNull([price month 8]), 0, 1) + if(IsNull([price month 9]), 0, 1)

  + if(IsNull([price month 10]), 0, 1) + if(IsNull([price month 11]), 0, 1) + if(IsNull([price month 12]), 0, 1))

  as [average price value]

;

LOAD

  [year of stock],

  [month of stock],

  [price value],

  if([month of stock] <= 1, ApplyMap('MapStock', [year of stock] & '-' & 1, Null()), Null()) as [price month 1],

  if([month of stock] <= 2, ApplyMap('MapStock', [year of stock] & '-' & 2, Null()), Null()) as [price month 2],

  if([month of stock] <= 3, ApplyMap('MapStock', [year of stock] & '-' & 3, Null()), Null()) as [price month 3],

  if([month of stock] <= 4, ApplyMap('MapStock', [year of stock] & '-' & 4, Null()), Null()) as [price month 4],

  if([month of stock] <= 5, ApplyMap('MapStock', [year of stock] & '-' & 5, Null()), Null()) as [price month 5],

  if([month of stock] <= 6, ApplyMap('MapStock', [year of stock] & '-' & 6, Null()), Null()) as [price month 6],

  if([month of stock] <= 7, ApplyMap('MapStock', [year of stock] & '-' & 7, Null()), Null()) as [price month 7],

  if([month of stock] <= 8, ApplyMap('MapStock', [year of stock] & '-' & 8, Null()), Null()) as [price month 8],

  if([month of stock] <= 9, ApplyMap('MapStock', [year of stock] & '-' & 9, Null()), Null()) as [price month 9],

  if([month of stock] <= 10, ApplyMap('MapStock', [year of stock] & '-' & 10, Null()), Null()) as [price month 10],

  if([month of stock] <= 11, ApplyMap('MapStock', [year of stock] & '-' & 11, Null()), Null()) as [price month 11],

  if([month of stock] <= 12, ApplyMap('MapStock', [year of stock] & '-' & 12, Null()), Null()) as [price month 12]

Resident Sampledata;

;

Drop Table Sampledata;

[raadwiptec]

hi sunny,

I have one more problem I have 13 th and 14th month

[sunny_talwar]

In a year you have 13th and 14th month?