topic Re: How to compute Average Stock ( Base on Jan 2015 Open Stock ) in QlikView

How to compute Average Stock ( Base on Jan 2015 Open Stock )

paulyeo11 — Thu, 24 Dec 2015 12:28:13 GMT

Hi All

I need to compute :-

when at Jan 2015 , Average Stock = 1 ( 1+1)/2=1 , which is Jan Open Stock + Jan Close Stock.

when at Feb 2015 , Average Stock = 1 ( 1+2)/2=1.5 , which is Jan Open Stock + Feb Close Stock.

when at Dec 2015 , Average Stock = 1 ( 1+12)/2=6.5 , which is Jan Open Stock + Dec Close Stock.

May i know how to do ?

Now my result on Average Stock is wrong.

Enclosed my QV Doc

Paul

Re: How to compute Average Stock ( Base on Jan 2015 Open Stock )

sunny_talwar — Thu, 24 Dec 2015 12:33:23 GMT

Try this expression:

RangeAvg(Above(Sum(STK_CLOSE), (RowNo() - 1)), sum(STK_OPEN))

RangeAvg(Above(Sum(STK_OPEN), (RowNo() - 1)), sum(STK_CLOSE))

Re: How to compute Average Stock ( Base on Jan 2015 Open Stock )

swuehl — Thu, 24 Dec 2015 12:34:14 GMT

Try as expression

(Column(2)+Top(Column(1)) )/2

Top() will evaluate Column(1) in the context of the top dimensional row, i.e, Jan in your case.

Re: How to compute Average Stock ( Base on Jan 2015 Open Stock )

swuehl — Thu, 24 Dec 2015 12:35:59 GMT

Another alternative would be to calculate the jan stock using set analysis:

(Column(2)+sum(TOTAL {<[date(date_t)] = {'1/1/2015'}>}STK_OPEN) )/2

Re: How to compute Average Stock ( Base on Jan 2015 Open Stock )

tresB — Thu, 24 Dec 2015 12:36:42 GMT

(Column(2)+sum(total {<[date(date_t)]={'1/1/2015'}>}STK_OPEN))/2

Re: How to compute Average Stock ( Base on Jan 2015 Open Stock )

Anonymous — Thu, 24 Dec 2015 12:37:37 GMT

like this?

Re: How to compute Average Stock ( Base on Jan 2015 Open Stock )

paulyeo11 — Thu, 24 Dec 2015 13:01:25 GMT

Hi All

Thank you so much for all your help.

Now i like to create expression using SET.

Meaning if i select month = 6 , i need the Average stock display as 3.5. , which is correct.

How to do it ?

As Now when i select month = 6 , it display 5.5

which is wrong.

Paul

Re: How to compute Average Stock ( Base on Jan 2015 Open Stock )

swuehl — Thu, 24 Dec 2015 13:07:13 GMT

The version with set analysis should work:

(Column(2)+sum(TOTAL {<[date(date_t)] = {'1/1/2015'}>}STK_OPEN) )/2

returns 3.5 when selecting june.

Re: How to compute Average Stock ( Base on Jan 2015 Open Stock )

paulyeo11 — Thu, 24 Dec 2015 13:18:20 GMT

Hi Sir

Now i like to create expression using SET.

Meaning if i select month = 6 , i need the Average stock display as 3.5. , which is correct.

How to do it ?

As Now when i select month = 6 , it display 3

which is wrong.

I also notice that the expression below :-

(Column(2)+sum(TOTAL {<[date(date_t)] = {'1/1/2015'}>}STK_OPEN) )/2

How to make 1/1/2015 change to 1/1/2014 , when i select year = 2014.

Paul

Re: How to compute Average Stock ( Base on Jan 2015 Open Stock )

Anonymous — Thu, 24 Dec 2015 13:23:00 GMT

modify this :

(Column(2)+sum(TOTAL {<[date(date_t)] = {'1/1/2015'}>}STK_OPEN) )/2

with it:

(Column(2)+sum(TOTAL {<[date(date_t)] = {'=$(=date(min(DateField)))'}>}STK_OPEN) )/2

Re: How to compute Average Stock ( Base on Jan 2015 Open Stock )

paulyeo11 — Thu, 24 Dec 2015 13:32:24 GMT

Hi Sir

I try your dynamic date SET expression :-

Now when i select month = 6 , it display 3 is wrong , it should be 3.5 , because (1+6)/2 = 3.5

Re: How to compute Average Stock ( Base on Jan 2015 Open Stock )

sunny_talwar — Thu, 24 Dec 2015 14:43:03 GMT

This?

Expression:

If(sum(STK_CLOSE) > 0 and sum(STK_OPEN) > 0, RangeAvg(Above(Sum({<month>}STK_OPEN), (RowNo() - 1)), sum({<month>}STK_CLOSE)))

Re: How to compute Average Stock ( Base on Jan 2015 Open Stock )

paulyeo11 — Thu, 24 Dec 2015 21:28:52 GMT

‌Hi All

many thank for all the expert here help me solve the problem. Wish all of you merry x'mas

paul

Re: How to compute Average Stock ( Base on Jan 2015 Open Stock )

paulyeo11 — Thu, 24 Dec 2015 23:00:16 GMT

Hi Sir

Now i try to plot the bar chart by year . As i need to view the Avg stock trend by year by month.

Since now i never select year = 2015 , and the result only working for 2014 and not 2015 .

Paul

Re: How to compute Average Stock ( Base on Jan 2015 Open Stock )

sunny_talwar — Thu, 24 Dec 2015 23:58:37 GMT

You can do this by fixing the load order in the script. I did a partial reload, you can do it with a regular resident load.

Script:

LOAD date(date_t),

num(month(date_t)) as [month],

year(date_t) as [year],

STK_OPEN,

STK_CLOSE

FROM

(ooxml, embedded labels, table is data);

FinalTable:

NoConcatenate

ADD Only

LOAD *

Resident data

Order By [date(date_t)];

DROP Table data;

Bar Chart:

Expression: Aggr(If(sum(STK_CLOSE) > 0 and sum(STK_OPEN) > 0, RangeAvg(Above(Sum({<month>}STK_OPEN), (RowNo() - 1)), sum({<month>}STK_CLOSE))), [date(date_t)], year)

Re: How to compute Average Stock ( Base on Jan 2015 Open Stock )

paulyeo11 — Fri, 25 Dec 2015 01:41:44 GMT

Hi Sunny

I open your QV Doc , i notice that for month = 12 , 2015

Amount shouls be 1+12=13 13/2=6.5 , but your equal to 11.5 is wrong.

same for 2014 also wrong.

Paul

Re: How to compute Average Stock ( Base on Jan 2015 Open Stock )

sunny_talwar — Fri, 25 Dec 2015 03:32:44 GMT

This?

Expression:

If(sum(STK_CLOSE) > 0 and sum(STK_OPEN) > 0,

RangeAvg(Aggr(Sum({<month>}TOTAL <year> If([date(date_t)] = YearStart([date(date_t)]), STK_OPEN)), [date(date_t)], year), sum({<month>}STK_CLOSE)))

Re: How to compute Average Stock ( Base on Jan 2015 Open Stock )

paulyeo11 — Fri, 25 Dec 2015 06:15:19 GMT

Hi Sunny

What are the purpose of below script ?

FinalTable:

NoConcatenate

ADD Only

LOAD *

Resident data

Order By [date(date_t)];

DROP Table data;

I guess you try to reload and try to sort by date it is ? Now with these code it does work on my side.

Paul

Re: How to compute Average Stock ( Base on Jan 2015 Open Stock )

paulyeo11 — Fri, 25 Dec 2015 11:30:11 GMT

Hi Sunny

I have apply your expression into my QV doc , i get some thing wrong , can you take a look ?

1,730,509 / 4,884,817 = 0.354 But QV Return 5.01