Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
This?
Expression:
If(sum(STK_CLOSE) > 0 and sum(STK_OPEN) > 0, RangeAvg(Above(Sum({<month>}STK_OPEN), (RowNo() - 1)), sum({<month>}STK_CLOSE)))
Hi All
many thank for all the expert here help me solve the problem. Wish all of you merry x'mas
paul
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
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)
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
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)))
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
Hi Sunny
I have apply your expression into my QV doc , i get some thing wrong , can you take a look ?