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
Answers


Okay, great!
is there a possibility to add a column and calculate the average over both?
Regards
Hendrik
0 
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
0 
Hi,
the first example (1.189+431+399+386+362)/5. I use a Detail by.
Regards
Hendrik
0 


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
0 
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 SUMFunktion 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
2 
Hi
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
0 
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
0 

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
0 
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
2 
0

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
3 
Awesome, thanks Björn Reuber
0 
Hi Hendrik Hellwig,
depending on your BOARD version (>= 10.0) you could also try the analytics functions:
BR,
Sebastian
1
Categories
 All Categories
 2K Forums
 1.8K Platform
 173 Academy
 331 Resources
 1 Board Knowledge Base
 50 Best Practices
 49 HowTo Guides
 19 Board Advocacy Program
 196 Blog
 4 Groups Hub
 4 About Groups
 New Community Members
 Board Community Captains
 DACH
 Japan
 5 Community Captains
 1 About Community Captains
 3 Meet the Community Captains
 1 Topics & Thought Starters
 Learn from the Board Captains
 Release Notes
 Academy
 2 Board Academy
 8 ILT/VILT Course Catalogue
 13 eLearning Course Catalogue
 4 Academy Forum
 1.3K Idea Exchange
 340 Partner Hub
 94 Support
 14 FAQ's
 Customer Support Portal
 54 Support Articles
 BEAP