nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

412195_en-US

liliconcom

Calculate difference between values in consecutive rows by group

Hi There,

I have a data table with 2 columns like the below:

group value
<SPAN class="hljs-number">1</SPAN>     <SPAN class="hljs-number">10</SPAN>
<SPAN class="hljs-number">1</SPAN>     <SPAN class="hljs-number">20</SPAN>
<SPAN class="hljs-number">1</SPAN>     <SPAN class="hljs-number">25</SPAN>
<SPAN class="hljs-number">2</SPAN>     <SPAN class="hljs-number">5</SPAN>
<SPAN class="hljs-number">2</SPAN>     <SPAN class="hljs-number">10</SPAN>
<SPAN class="hljs-number">2</SPAN>     <SPAN class="hljs-number">15</SPAN>

I want to create a new column, "diff", that calculates the differences between consecutive rows, grouped by "group", so I would have something like this:

group value diff
<SPAN class="hljs-number">1</SPAN>     <SPAN class="hljs-number">10</SPAN>    . <SPAN class="hljs-comment"># because there is a no previous value</SPAN>
<SPAN class="hljs-number">1</SPAN>     <SPAN class="hljs-number">20</SPAN>    <SPAN class="hljs-number">10</SPAN> <SPAN class="hljs-comment"># value[2] - value[1]</SPAN>
<SPAN class="hljs-number">1</SPAN>     <SPAN class="hljs-number">25</SPAN>    <SPAN class="hljs-number">5</SPAN>  <SPAN class="hljs-comment"># value[3] value[2]</SPAN>
<SPAN class="hljs-number">2</SPAN>     <SPAN class="hljs-number">5</SPAN>     . <SPAN class="hljs-comment"># because group is changed</SPAN>
<SPAN class="hljs-number">2</SPAN>     <SPAN class="hljs-number">10</SPAN>    <SPAN class="hljs-number">5</SPAN>  <SPAN class="hljs-comment"># value[5] - value[4]</SPAN>
<SPAN class="hljs-number">2</SPAN>     <SPAN class="hljs-number">15</SPAN>    <SPAN class="hljs-number">5</SPAN>  <SPAN class="hljs-comment"># value[6] - value[5]</SPAN>

</CODE><CODE class="hljs language-r">

Please can you help me with the jsl for the column formula?

Many thanks,

Alicia

Data Table

Scripting

Re: Calculate difference between values in consecutive rows by group

If the table is always sorted by group like your example you could do this with an if statement:

If( Lag( :group ) == :group,
 :value - Lag( :value ),
 .
)