8 Replies Latest reply: Mar 29, 2015 7:47 AM by Yeo Poh sai

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

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

• ###### Re: SET expression compute average stock using open stock and close stock field.

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

• ###### Re: SET expression compute average stock using open stock and close stock field.

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

• ###### Re: SET expression compute average stock using open stock and close stock field.

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
• ###### Re: SET expression compute average stock using open stock and close stock field.

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

• ###### Re: SET expression compute average stock using open stock and close stock field.

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

• ###### Re: SET expression compute average stock using open stock and close stock field.

Hi Sir

Thank you very much , it work.

Paul

• ###### Re: SET expression compute average stock using open stock and close stock field.

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

• ###### Re: SET expression compute average stock using open stock and close stock field.

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