How can I calculate an average stock in my dataview?

Hi,

can someone help me calculating an average stock in my dataview? I have a monthly stock information and I want to divide it by number of months <> 0. I tried with a refer to but I can not calculate with the sum. If I try to divide 'a' by 'b' board does not use the total sum, for example 4 (first row), board divides by 1:

Thank you!

regards

Hendrik Hellwig

Find more posts tagged with

Accepted answers

All comments

Björn Reuber

Hi,

you can easily solve this, using nexel:

=AVERAGEEX( [@a;*;*;Range.Horizontal] )

Or maybe the "Yearly Moving Average" function might also be helpful

Regards

Björn

Hendrik Hellwig

Okay, great!

is there a possibility to add a column and calculate the average over both?

Regards

Hendrik

Björn Reuber

Hi,

should it be a weighted Average? so for your case, shout it be (1.189+431+399+386+362)/5 or should it be (1.189+(431+399+386+362)/4)/2?

Do you use an by Column entity or a Detail by ?

Regards

Björn

Hendrik Hellwig

Hi,

the first example (1.189+431+399+386+362)/5. I use a Detail by.

Regards

Hendrik

Björn Reuber

Hi,

=([@a;*;*]+SUM( [@b;*;*;Range.Horizontal] ))/([@a;*;*;CountEX.Horizontal]+[@b;*;*;CountEX.Horizontal])

should work (havn't tried it with detail by)

Regards

Björn

Hendrik Hellwig

Hi,

I tried =(SUM([@a;*;*])+SUM( [@b;*;*;Range.Horizontal] ))/([@a;*;*;CountEX.Horizontal]+[@b;*;*;CountEX.Horizontal]).

The SUM-Funktion works but the CountEX.Horizontal only works at column b not a.

At column b there is the 'Detail by', at column a I use a 'Refer to'.

Regards

Hendrik

Björn Reuber

Hi,

did it worked with the formula I posted? Cause the first sum in yours can't work, cause no area is defined.

Regards

Björn

Hendrik Hellwig

Hi,

you are right. I used the formula

=(SUM([@a;*;*;Range.Horizontal])+SUM( [@b;*;*;Range.Horizontal] ))/([@a;*;*;CountEX.Horizontal]+[@b;*;*;CountEX.Horizontal]),

because your formula

=([@a;*;*]+SUM( [@b;*;*;Range.Horizontal] ))/([@a;*;*;CountEX.Horizontal]+[@b;*;*;CountEX.Horizontal])

does not work.

Then I had this problem: The SUM-Funktion works but the CountEX.Horizontal only works at column b not a.

I tried a little bit and now I choose this function and it works:

=(SUM([@a;*;*;Range.Horizontal])+SUM( [@b;*;*;Range.Horizontal] ))/[@a;*;*;CountEX.Left]

Regards

Hendrik

Hendrik Hellwig

I do not understand why the formula is not working:

The blue one is working. Here the formula is =[@i;*;*]. It is the Block 'ø-Bestand Menge 2017'.

Can you help?

Regards

Hendrik

Björn Reuber

Hi Hendrik,

it's quite hard to answer your question, without knowing details about the Layout, cause it can have several reasons (different Block, Detail by, so the other block has a different number of columns....).

maybe you can post a screenshot of the alyout configution and add the Block Number (a, b, c...) to the Block heading, so its easier to distinguish the different blocks.

Cause without knowing, if this a different blocks it is very hard to help you

regards

Björn

Hendrik Hellwig

is there a possibility to link two formulas like

[@a;*;*] and [@b;*;*;Count.Left]

to [@a;*;[@b;*;*;Count.Left]]?

Regards

Hendrik

Björn Reuber

Hi Hendrik,

I never have tested it, but what do you want to do? Cause with your syntax you would point to the cell with the Code [@b;*;*;Count.Left] (Absolute Reference Mode), and you dont know if an Enitty with this code eixst