Relative reference to columns

[aaron_sakovich]

There's something I'm just not getting today...

I've got a chart command that generates results from a series of searches, evals, and other processes. The net result is a nice little chart with results that looks like this:

Location     2019    2020    Delta
Main          980   1268    29.39 %    

The 2019 and 2020 are indeed years. My issue is that Delta is calculated based on those 2 columns as

 eval Delta=(('2020'-'2019')/'2019'*100)

This is fine for this year, but of course it means we'd have to edit this dashboard again next year.

How do I reference the relative column positions rather than the column names, or otherwise glean the column names from the dynamic data, in order to crunch the Delta value automagically?

[somesoni2 · Accepted solution]

Try something like this

Your current chart command giving Location data for Year i.e. fields Location, <last year>,<current year>
| eval Delta=null()
| foreach 2* [| eval Delta=if(isnull(Delta),'<<FIELD>>' ,(('<<FIELD>>'-'Delta')*100/'Delta' ]

Assuming your output contains fields Location, 2019, 2020 (in the very same order, order is important here). Then the foreach will run 2 passes- one for 2019 and one for 2020. When it runs for first column '2019', the Delta is null, so '2019' is assigned to Delta. On 2nd pass for '2020', Delta is not null, so it'll calculate the % Delta based on '2020' as compared to current Delta which is '2019' value.

[aaron_sakovich]

So very close -- only thing missing was a couple closing parens!

 | eval Delta=null()
 | foreach 2* [| eval Delta=if(isnull(Delta),'<<FIELD>>' ,(('<<FIELD>>'-'Delta')*100/'Delta')) ]

TYVM!!! I'm happy for another 979 years!