How to compute Average Stock ( Base on Jan 2015 Open Stock )

[paulyeo11]

Hi All

I need to compute :-

when at Jan 2015 , Average Stock = 1 ( 1+1)/2=1 , which is Jan Open Stock + Jan Close Stock.

when at Feb 2015 , Average Stock = 1 ( 1+2)/2=1.5 , which is Jan Open Stock + Feb Close Stock.

..

..

when at Dec 2015 , Average Stock = 1 ( 1+12)/2=6.5 , which is Jan Open Stock + Dec Close Stock.

May i know how to do ?

Now my result on Average Stock is wrong.

Enclosed my QV Doc

Paul

[sunny_talwar]

Try this expression:

RangeAvg(Above(Sum(STK_CLOSE), (RowNo() - 1)), sum(STK_OPEN))

RangeAvg(Above(Sum(STK_OPEN), (RowNo() - 1)), sum(STK_CLOSE))

[swuehl]

Try as expression

(Column(2)+Top(Column(1)) )/2

Top() will evaluate Column(1) in the context of the top dimensional row, i.e, Jan in your case.

[swuehl]

Another alternative would be to calculate the jan stock using set analysis:

(Column(2)+sum(TOTAL {<[date(date_t)] = {'1/1/2015'}>}STK_OPEN) )/2

[tresB]

(Column(2)+sum(total {<[date(date_t)]={'1/1/2015'}>}STK_OPEN))/2

[Anonymous]

like this?

[paulyeo11]

Hi All

Thank you so much for all your help.

Now i like to create expression using SET.

Meaning if i select month = 6 , i need the Average stock display as 3.5. , which is correct.

How to do it ?

As Now when i select month = 6 , it display 5.5

which is wrong.

Paul

[swuehl]

The version with set analysis should work:

(Column(2)+sum(TOTAL {<[date(date_t)] = {'1/1/2015'}>}STK_OPEN) )/2

returns 3.5 when selecting june.

[paulyeo11]

Hi Sir

Now i like to create expression using SET.

Meaning if i select month = 6 , i need the Average stock display as 3.5. , which is correct.

How to do it ?

As Now when i select month = 6 , it display 3

which is wrong.

I also notice that the expression below :-

(Column(2)+sum(TOTAL {<[date(date_t)] = {'1/1/2015'}>}STK_OPEN) )/2

How to make 1/1/2015 change to 1/1/2014 , when i select year = 2014.

Paul

[Anonymous]

modify this :

(Column(2)+sum(TOTAL {<[date(date_t)] = {'1/1/2015'}>}STK_OPEN) )/2

with it:

(Column(2)+sum(TOTAL {<[date(date_t)] = {'=$(=date(min(DateField)))'}>}STK_OPEN) )/2