SET expression compute average stock using open stock and close stock field.

[paulyeo11]

Hi All

I have this SET expression (Table 2) to display STOCK field to compute average amount. which is working fine.

Avg({$<year = {$(=max(year))}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>} (STOCK)/1)

But My accountant not allow me using STOCK field and need me using Open stock and Close stock field to compute the average stcok by month. ( See Table 1 )

Any idea how to modify the above expression ?

Note that i cannot use Table 1 expression , as it is not SET expression.

Paul

[paulyeo11]

Hi Sir

I forget to info you

For Jan , need to compare Jan Open Stock and Jan close Stock.

For Feb , need to compare Jan Open Stock and Feb close Stock.

For Mar , need to compare Jan Open Stock and Mar close Stock

Hope some one can advise me.

Paul

[MK_QSL]

In Table 2 What is the Result (STOCK Value) you are expecting?

[paulyeo11]

Hi sir

When click month=Feb , i need the Table 2 display (200+50)/2=125

When click month=Jan , i need the Table 2 display (200+100)/2=150

YearMonth	STK_OPEN	STK_CLOSE
	300	150
15 Jan	200	100
15 Feb	100	50

[MK_QSL]

HOW?

When click month=Feb , i need the Table 2 display (200+50)/2=125

When click month=Jan , i need the Table 2 display (200+100)/2=150

[#6 · Accepted solution]

Hi,

Have you tried:

Avg({$<year = {$(=max(year))}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>}(STK_OPEN+STK_CLOSE)/2 )

Seems to work fine.

Hope this helps.

Kind regards,

Nuno

[paulyeo11]

Hi Sir

Thank you very much , it work.

Paul

[MK_QSL]

Glad that you have got your answer but I still don't understand whether you will get below result or not?

Feb  (200+50)/2=125

Jan  (200+100)/2=150

[paulyeo11]

Hi Sir

I don't get what I want. As it just average by month. Does not in use Jan month as open stock and end of Feb close stock when I click on month equal to feb. What it does is using jan and feb open and close amount . Which is not what I want. Any suggestions? 

Paul

Sent from Samsung Mobile