How can I calculate an average stock in my dataview?

Hendrik Hellwig
Hendrik Hellwig
Fourth Anniversary 10 Comments 5 Likes
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
    500 Likes Fourth Anniversary 100 Comments 5 Answers
    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
    Hendrik Hellwig
    Fourth Anniversary 10 Comments 5 Likes
    edited March 2020

    Okay, great!

     

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

    image

    Regards 

    Hendrik

  • Björn Reuber
    Björn Reuber Employee, Community Captain
    500 Likes Fourth Anniversary 100 Comments 5 Answers

    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
    Hendrik Hellwig
    Fourth Anniversary 10 Comments 5 Likes

    Hi,

     

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

     

    Regards

    Hendrik

  • Björn Reuber
    Björn Reuber Employee, Community Captain
    500 Likes Fourth Anniversary 100 Comments 5 Answers

    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
    Hendrik Hellwig
    Fourth Anniversary 10 Comments 5 Likes

    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
    Björn Reuber Employee, Community Captain
    500 Likes Fourth Anniversary 100 Comments 5 Answers

    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
    Hendrik Hellwig
    Fourth Anniversary 10 Comments 5 Likes

    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
    Hendrik Hellwig
    Fourth Anniversary 10 Comments 5 Likes
    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

  • Björn Reuber
    Björn Reuber Employee, Community Captain
    500 Likes Fourth Anniversary 100 Comments 5 Answers

    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
    Hendrik Hellwig
    Fourth Anniversary 10 Comments 5 Likes

    Hi

     

    is there a possibility to link two formulas like

     

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

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

     

    Regards

    Hendrik

  • Björn Reuber
    Björn Reuber Employee, Community Captain
    500 Likes Fourth Anniversary 100 Comments 5 Answers

    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

  • Etienne CAUSSE
    Etienne CAUSSE Customer
    Fourth Anniversary 250 Up Votes 100 Comments 100 Likes

    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

  • Previous Member
    Previous Member
    500 Up Votes 500 Likes 500 Comments Second Anniversary

    Hi Björn Reuber,

     

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

     

    BR, Ray

  • Björn Reuber
    Björn Reuber Employee, Community Captain
    500 Likes Fourth Anniversary 100 Comments 5 Answers

    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

  • Previous Member
    Previous Member
    500 Up Votes 500 Likes 500 Comments Second Anniversary

    Awesome, thanks Björn Reuber

  • Sebastian Gurt
    Sebastian Gurt Employee
    Fourth Anniversary 100 Likes 10 Comments 25 Up Votes
    edited March 2020

    Hi Hendrik Hellwig,

     

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

     

    image

     

    BR,

     

    Sebastian

Categories