How can I calculate an average stock in my dataview?

Hendrik Hellwig
edited April 2020 in Platform

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:

image

Thank you!

 

regards

Hendrik Hellwig

Answers

  • Björn Reuber
    Björn Reuber Employee
    Community Captain First Answer First Anniversary 5 Up Votes
    edited March 2020

    Hi,

     

    you can easily solve this, using nexel:

     

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

    image

     

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

     

    Regards

    Björn

  • Hendrik Hellwig
    edited March 2020

    Okay, great!

     

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

    image

    Regards 

    Hendrik

  • 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

  • Hi,

     

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

     

    Regards

    Hendrik

  • Hi,

     

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

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

     

    Regards

    Björn

  • 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

  • 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

  • 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
    edited March 2020

    Hi

     

    I do not understand why the formula is not working:

     

    image

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

     

    Can you help?

     

    Regards 

    Hendrik

  • 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

  • Hi

     

    is there a possibility to link two formulas like

     

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

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

     

    Regards

    Hendrik

  • 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

     

    Regards

    Björn

  • Hi,

    I had the same kind of question but I'm not using Nexel. Instead you can use a "counter" cube, that can be quickly filled by a dataflow such as :

    a: stock cube

    b: counter

     

    dataflow: b=a^0

    (which is 1 where there is a value and 0 when no value).

     

    then in your layout you can use the counter cube as your divider instead of trying to find the right total in the layout.

     

    Etienne

  • Hi Björn Reuber,

     

    how would you do this for charts. AFAIK they don't support formulas.

     

    BR, Ray

  • Hi,

    you can create the Layout including a nexel formula in a dataview and then copy this Layout to another object (like a chart)

     

    Regards

    Björn

  • Sebastian Gurt
    Sebastian Gurt Employee
    First Anniversary DACH Level 100: Foundations of Building in Board Level 200: Leveraging Board for Business Insights
    edited March 2020

    Hi Hendrik Hellwig,

     

    depending on your BOARD version (>= 10.0) you could also try the analytics functions:

     

    image

     

    BR,

     

    Sebastian