topic Re: SET expression compute average stock using open stock and close stock field. in QlikView

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

paulyeo11 — Sat, 28 Mar 2015 19:49:54 GMT

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.

paulyeo11 — Sat, 28 Mar 2015 19:54:17 GMT

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.

MK_QSL — Sat, 28 Mar 2015 20:17:09 GMT

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.

paulyeo11 — Sat, 28 Mar 2015 20:21:17 GMT

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.

MK_QSL — Sat, 28 Mar 2015 20:37:08 GMT

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.

Sat, 28 Mar 2015 22:36:31 GMT

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.

paulyeo11 — Sun, 29 Mar 2015 04:14:58 GMT

Hi Sir

Thank you very much , it work.

Paul

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

MK_QSL — Sun, 29 Mar 2015 07:09:54 GMT

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.

paulyeo11 — Sun, 29 Mar 2015 11:47:19 GMT

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