Qlik Community

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.
paulyeo11
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
paulyeo11
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
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?

paulyeo11
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

YearMonthSTK_OPENSTK_CLOSE
300150
15 Jan200100
15 Feb10050
MVP
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

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

paulyeo11
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

Community Browser