12 Replies Latest reply: Apr 27, 2016 9:12 AM by Yeo Poh sai

# How to plot the bar only 1 bar per year ?

Hi All

Table 1 have stock amount by month from 1996 Jan till 2016 march.

Chart 1 shown the bar chart for stock amount from 1996 Jan till 2016 March.

My question is how to make the chart 1 to display average stock for only every mach ?

Meaning it will display bar show as following :-

2016 march

2015 march

2014 march

and so on...

Paul

my QVF

• ###### Re: How to plot the bar only 1 bar per year ?

Paul, not sure I understand what you are trying to do my friend. Can you may be elaborate a little more please?

• ###### Re: How to plot the bar only 1 bar per year ?

Hi sunny

I want to show only 26 bar instead of 26*12 bar.

Meaning it will display every year only March sales . Since now my data max month = March

So that I can compare sales by year.

Alternative if can compute the yearly stock amount even more better.

Meaning sum of 2016 divide by 3 , show a bar for 2016.

And sum of 2015 stock amount divide by 12 , show 1 bar .

My Objective is to display stock trend amount by month.

Paul

Sent from my iPhone

• ###### Re: How to plot the bar only 1 bar per year ?

Try this:

Money(

Only({<year, month = {"\$(=Max({<year = {\$(=Max(year))}>}month))"}>}Aggr(RangeSum(

Above(RangeSum(Above(Sum({<year, month , GL_CODE = {50010}>}[Amount]*1), 0, RowNo()))),

-Sum({<GL_CODE = {50010}>}TOTAL Aggr(Sum({<year, month, GL_CODE = {50010}>}[Amount]*1), YearMonth))), (YearMonth, (NUMERIC, DESCENDING))))

, \$(vMoneyFormatK_GL))

• ###### Re: How to plot the bar only 1 bar per year ?

For yearly, I would need to know what the expected output is so that I can have a goal in mind

• ###### Re: How to plot the bar only 1 bar per year ?

Hi sunny

Average stock amount by year :-

2016 = 1.668 million

2017 = 1.755 million

Basically I sum up all the stock amount for 2015 divide by 12. This will give me average stock amount for 2015. That go same with the rest of the year.

For March 2016 I need to decide by 3 since it is only 3 month.

Sent from my iPhone

• ###### Re: How to plot the bar only 1 bar per year ?

Is the dimension Year here?

Numbers are slightly different then what you were seeking, but can you check this (seems to match if I add each month number and divide it by number of months in that year)

• ###### Re: How to plot the bar only 1 bar per year ?

Hi Sunny

Look like the average amount is wrong compare with my manually compute average amount.

Alternative , can you take Jan amount add Dec amount and get the average amount. As accounting people compute average stock amount using this approach. meaning :-

2015 Jan = 1.91 million

2015 Dec = 1.61 million

Average stock amount = (1.91 + 1.61 ) /2 = 1.76

Paul

• ###### Re: How to plot the bar only 1 bar per year ?

I see 1,306,777.49 for 15 Dec and 1,601,154.14 for 15 Jan. How are you calculating the numbers 2015 Jan and 2015 Dec numbers on your end?

Additionally how will you calculate the average for 2016 where dec is not available yet?

• ###### Re: How to plot the bar only 1 bar per year ?

Hi sunny

I think I need to start with Jan 1996 month to compute the closing stock amount and using the value as opening stock for Feb 1996 and sun the stock transaction of Feb to compute the closing stock for 1997 Feb and so on. I think if I give you the info you should be able to give me what I want on average stock.

Now I need to enter the plane . Thank you very much .

Paul

Sent from my iPhone

• ###### Re: How to plot the bar only 1 bar per year ?

Hi Sunny

For 2016 , since it only have 3 month , so i need to divide by 3.

Paul

• ###### Re: How to plot the bar only 1 bar per year ?

Hi Sunny

After rest for few days , i test on the your expression again , this time i notice that the yearly stock number is correct.

Thank

Paul