# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Valued Contributor II

## 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

1 Solution

Accepted Solutions
Not applicable

## 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

8 Replies
Valued Contributor II

## 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

MVP

## 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?

Valued Contributor II

## 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
MVP

## 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

Not applicable

## 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

Valued Contributor II

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

Hi Sir

Thank you very much , it work.

Paul

MVP

## 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

Valued Contributor II

## 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