Regarding Creating custom column with Percentage growth

[bhavinshah1086]

Hello team,

I am using Kibana Enhanced Table 7.17 version and created following table using (Date Histogram (1 day interval) and sum of revenue

in below table, to present requirement / expectation, I have calculated custom column in excel as ((B4/B3)/1).

Need your help to create similar column in Enhanced Table as well.

[fbaligand]

Well, I'm not sure to understand your Excel formula ((B4/B3)/1)
Given your example array, could you detail how you compute 36%?

[bhavinshah1086]

Hi,

Turnover column's ((Row2 / row1)- 1) . For your reference, please find below computation for first cell in column 3.

I hope this information helps you to understand it.

Regards
Bhavin.

[fbaligand]

Hi,

Well, it's much clearer to me now.
So, basically, with Enhanced Table, on a computed column formula, you can't access data from another row.

But you can achieve your goal using "Serial Diff" pipeline aggregation:

• add a new "Metric Aggregation" after "Turnover" metric aggregation. Choose "Serial Diff", and as embedded aggregation, choose the same aggregation than "Turnover" metric.
• then in your computed column "% change over previous day", you set this formula:
  col2 ? col1 / (col1 - col2) - 1 : null
• finally, in "Hidden columns" setting, set 2
• you're done! It should show the same result than your Excel spreadsheet.

[bhavinshah1086]

Hi, thanks alot.... Seems easy..... Will surely try and share my feedback.

Regards

[bhavinshah1086]

Hi, I am able to get desired result but now the challenge is, I had to use first column as date histogram and it is not allowing to skip Saturday and Sundays and that creates impact on serial diff value on Monday.... Is there any workaround for this...

I have already added custom field for week number and added in filter but it is not skipping as date histogram filter is applied as one day.

Is there any workaround on this😂😂

Regards

[fbaligand]

First, happy to see that my suggestion answers your need!

Then, for your second problem, unfortunately, I have no solution.
As you say, date histogram splits data per day, and to my known, there is no way to configure it to skip week-end days.

Maybe you could have a scripted field dayOfWeek and add a filter in search bar to avoid Saturday and Sunday days of week.
Then, in bucket advanced settings, you can set :
{"min_doc_count": 1}

Maybe it could let you skip Saturday and Sunday buckets.
No warranty…

[bhavinshah1086]

Hi, sorry for late response but I did try that but could not get any success on the same. Thinking of using some rollup indexes to have calculated details from before hand. Although, thanks alot for your support.

Yet, your Enhanced Table is amazing thing in Kibana. Would like to see some of Visualization Widgets for displaying those details in more intuitive way.

Thanks & Best Regards.

[kobi2c]

I add the same problem and solved it by writing colChange function. (see #280 , tested on OSD)