How to rename column title in the Layout || Nexel ?

Options
[Previous Member]
[Previous Member]
First Comment First Anniversary
edited April 2020 in Platform

Hi, 

 

The following DataView shows the salary per employee.

My last column which is called « Total » represents the average per employee.

image

In the Layout, I change « sum » by « average ».

image

So, my problem is : the result on the display is ok but the title is still « Total » instead of « Average » or something I would like. For me, the title does not corresponding with the data.

How can I rename it ?

And, more broadly, how can I rename entities on a specific objet, according to the context for example in the Nexel ?

 

 

Thanks by advance,

 

 

Have a great day,

Tagged:

Answers

  • Paul Wyatt
    Paul Wyatt Customer
    First Comment 5 Up Votes 5 Likes First Anniversary
    Options

    Hi C Tnu

     

    You could hide the TOTAL column and sum the rows with an algorithm block which you can title anything you like.

     

    Paul Wyatt

  • [Previous Member]
    [Previous Member]
    First Comment First Anniversary
    edited March 2020
    Options

    Hi Paul ! 

     

    I tried to use the most simple way to do that but you're right. 

     

    I've got some difficulties to use Nexel, I never did it before, I'm trying to understand.

     

    You can see below, to me, the formula should work but it doesn't, my last column just copy the first column of values when I would like the average of this four years (if values <> blank).

    image

     

     

     

    Thanks by advance, 

    I will continue..

  • Paul Wyatt
    Paul Wyatt Customer
    First Comment 5 Up Votes 5 Likes First Anniversary
    edited March 2020
    Options

    Hi C Tnu 

     

    The Nexel formulation you need to achieve an average of the rows is AVERAGEIF([range],["criteria"]).  Image 1 shows a table listing row items.  Each item may have upto three amounts attributed.  The last 'average' column calculates the average of the contents of the three amount rows where the amount is over 0. 

     

    Image 2 shows the formula used.  Points to note about the formula are that the command is AVERAGEIF() which has two arguments, unlike AVERAGE() which only has one.  The first argument is the range of columns within the row to be calculated whilst the second argument is the criteria that each range value must meet.  The second argument must be enclosed in double quotes " " in order to work.  So the formula used in this example is:

    =averageif([@d;*;*]:[@f;*;*],">0")

     

    IMPORTANT: You should also note that if you make a change to the block structure, such as adding, deleting or moving a column, you should always re-validate your NEXEL formulas which are static, unlike EXCEL which can dynamically update to compensate for such action.

     

    Image 1: Sampe data and the average of all shown amounts for each row.

    image

    Image 2: Nexel editor displaying the formula used to determine the Average of a row IF each range value meets a specified criteria.  Note that the criteria argument musts be enclosed in double quotes.  So, for totals greater than zero, the criteria is entered as ">0" and not >0.  You might also want to use "<>0" to exclude all null values but include negative values.

    image

     

    Hope this helps C Tnu,

     

    Regards,

     

    Paul

  • Dietmar Jeschkeit
    Dietmar Jeschkeit Active Partner
    First Anniversary Level 100: Foundations of Building in Board First Comment 5 Up Votes
    Options

    Hi C Tnu,

     

    if your salaries are one block and year is by column you can achive the average <> 0 in nexel with =AVERAGEEX( [@a;*;*;Range.Horizontal] ) where a is the block salaries. AVERAGEEX is the average without zero and Range.Horizontal means the whole row of the block a.

     

    BR

    Dietmar

  • [Previous Member]
    [Previous Member]
    First Comment First Anniversary
    edited March 2020
    Options

    Oh Paul I'm really grateful for your kindness and patience. 

     

    I go crazy, I tried almost everything and my Nexel still doesn't work. I dont know if it's about the language (work in French).. 

     

    image

     

    My last column stay empty. I tried with averagefi in french, such as Excel, tried to change commas, colons and so on..

     

     

    I find that Board is not really intuitive.. or maybe am I really crazy ? 

  • Dietmar Jeschkeit
    Dietmar Jeschkeit Active Partner
    First Anniversary Level 100: Foundations of Building in Board First Comment 5 Up Votes
    Options

    Hi,

    maybe you can give a bit more Information about your layout. Especially the blocks, entities by column. Have you tried my post: =AVERAGEEX( [@a;*;*;Range.Horizontal] ) where a is the block salaries ?

     

    BR

    Dietmar

  • [Previous Member]
    [Previous Member]
    First Comment First Anniversary
    edited March 2020
    Options

    Hi Dietmar Jeschkeit, thank you for your help ! 

    So you're completely right, my columns entities are salaries per year (2016 to 2019). 

    I tried with AVERAGEEX but still not working. Maybe I forgot something.

    image

     

    If you're working on Board 11, could I have a screen example of one of ur Nexel formula to compare ? 



    Thank you by advance, 

     

     

    Have a nice day,

  • Dietmar Jeschkeit
    Dietmar Jeschkeit Active Partner
    First Anniversary Level 100: Foundations of Building in Board First Comment 5 Up Votes
    Options

    Hi,

     

    please check if AVERAGE is working. Seems that AVERAGEEX is not working. I´ve tried it in 11.1. Then you have to open a ticket for the support Team of BOARD.

  • [Previous Member]
    [Previous Member]
    First Comment First Anniversary
    edited March 2020
    Options

    yes AVERAGE is working but I would like something like AVERAGEIF because sometimes I've got blank (for example, an employee who worked for a year) and I don't want to count this blank. 

    The problem with AVERAGE is that it divide by the number of column without any rule. And AVERAGEIF is not working.

     

     

    For example, in that case, the last column is "AVERAGE". So it count 5030.30/4 = 1257.58 whereas I want 5030.30 in average in that case. 

    image

     

     

     

     

    Thank you !!

  • Dietmar Jeschkeit
    Dietmar Jeschkeit Active Partner
    First Anniversary Level 100: Foundations of Building in Board First Comment 5 Up Votes
    edited March 2020
    Options

    Hi,

     

    I tried some things.

     

    Here is my solution: =sum([@a;*;*;Range.Horizontal])/[@a;*;*;CountEX.Horizontal] 

    CountEX is counting the cells <> 0 !

     

    image

     

    Hope it helps !

  • Paul Wyatt
    Paul Wyatt Customer
    First Comment 5 Up Votes 5 Likes First Anniversary
    edited March 2020
    Options

    Hi C Tnu,

    Sorry for providing a BOARD 10 example.  Have you selected 'Single Formula'? This is an extra step required for BOARD 11 that was a default setting on BOARD 10.

     

    AVERAGEIF does work in BOARD 11 in the exact same way as I've shown; I don't think the NEXEL engine has changed, just the BOARD interface to access it.  

     

    To avoid any confusion, here are the steps to enter the formula you require in BOARD 11.x:

     

    1.   Select the dataview to activate it.

    -----------------------------------------------------

    2.   Select FX to access Nexel

    -----------------------------------------------------

    3.   Select [+BLOCK] to add a new blockimage

    -----------------------------------------------------

    4.   Enter a Block Heading and select 'Single formula' image

    ------------------------------------------------------

    5.   Double click in first cell of new block (column) to enable the formula entry area: image

    ------------------------------------------------------

    6.   To enter the formula for an 'averageif', type as follows:

          a.   AVERAGEIF(

          b.   Select first column of range: [@b;*;*]

          c.   Enter colon symbol - ':'

          d.  Select last column of range: [@d;*;*]

          e.   Enter a single comma ','

          f.   Enter condition in double quotes: "<>0"

          g.   Close bracket: ')'

          h.   Select the green TICK mark to commit formulation.

     

    If you were looking for an average of three row values but only where a value exists, your formula should now look like the line shown below, in the FX area

    image

     

    Let me know how you get on.

     

    Regards,

     

    Paul

  • Dietmar Jeschkeit
    Dietmar Jeschkeit Active Partner
    First Anniversary Level 100: Foundations of Building in Board First Comment 5 Up Votes
    Options

    Hi Paul Wyatt - Avison Young UK,

     

    i think the layout from C Tnu is different to yours. He hasn´t got 3 (or more) blocks. He has one block and an entity by column. So I think the solution with sum and countex is working for him (listed in the thread - =sum([@a;*;*;Range.Horizontal])/[@a;*;*;CountEX.Horizontal] ). Moreover the solution with sum and countex is more flexibel (if he select more years the Formula will work nevertheless).

     

    BR

    Dietmar

  • Paul Wyatt
    Paul Wyatt Customer
    First Comment 5 Up Votes 5 Likes First Anniversary
    Options

    Hi C Tnu and many thanks to Dietmar Jeschkeit

     

    Firstly, C Tnu.  I apologise for missing the point that you're using a single cube, detailed by the year entity; I should not try to answer posts whilst on holday and using a tablet :-).  However, the formula and solution from Dietmar Jeschkeit works so please check the brackets you're using and ensure you've set the formula type.  I had the same issues that you described initially.  I then entered each part of the formula separately to test them.  Once happy, I composited the formulae into one new block.

     

    Dietmar Jeschkeit - Thank you for illustrating how it is possible to sum on a single cube which is detailed by an entity.  I had no idea that this was possible and so you have expanded my understanding of NEXEL; I really can't wait to get back to work and begin experimenting with this feature.  I've come to realise that I've also never used the NEXEL specific formulations, relying only on my knowledge of EXCEL formulation which I then converted to work in NEXEL.  My approach to C Tnu's problem, for example, would have been to use duplicated cubes for the blocks, restricting each with a selection.  Clearly there are major drawbacks to this approach like what happens when a new entity member is used and that it becomes unwieldly with more than several members and so is hard to scale.

     

    Kind regards to you both for problem and knowledge sharing.

     

    Paul Wyatt

  • [Previous Member]
    [Previous Member]
    First Comment First Anniversary
    Options

    Dietmar Jeschkeit thank you so much, it's working (=sum([@a;*;*;Range.Horizontal])/[@a;*;*;CountEX.Horizontal]), it's a simple formula it's perfect !

     

    Paul Wyatt - Avison Young UK  sorry Paul, I should explain in more detail my situation. My principal current difficulty is that I was trained on the older version of Board so I'm trying to cross-check the information. Thank you so much for your patience, you're a good teacher ! 

     

    Have a nice day, 

Categories