Kibana Canvas: Comparing current month Event counts to previous month counts and result to be in a form of string "increase" or "Decrease"

[Solomon_k (solomon koujalgi)]

Hi Guys,

I am trying to create a Canvas page for Data Findings for the current month which compares Data records from the Previous Month and outputs the Result as "Increase" if the number of counts are greater then the Previous month, and Reults as "Decrease" if number of counts are Less compared to the Previous month.

For this I have a SQL Query in place:

SELECT "calcltd.monthNumber",count(*) As CurrentMonth,

"calcltd.monthName",Count(*) As PreviousMonth,

Case

When

Count("calcltd.monthNumber") > Count("calcltd.monthName")

Then
CAST ('Increase' As String)

When

Count("calcltd.monthNumber") < Count("calcltd.monthName")

Then
'Decrease'

Else 'Equal'

End
As Measure

FROM "events-appdb-*"

group by "calcltd.monthName","calcltd.monthNumber"

The Issue here is i am currently using a Global Time Filter group for all the canvas pages- "Time"(Selected for 1 month complete) and using the field "calcltd.monthNumber" ( which gives me the result for the current month)

In order to refer to the Previous month im using a Dropdown Select Filter "calcltd.monthName" which is Grouped under a different Filtergroup-"Month"

Is there any way that i can recall the filter group values in the expression for the Fields "calcltd.monthNumber" and "calcltd.monthName",from thier respective Filter groups.

Currently this the Expression Editter.

filters "Time" "Month-01" ungrouped=true
| essql
query="SELECT "calcltd.monthNumber",count(*) As CurrentMonth,

"calcltd.monthName",Count(*) As PreviousMonth,

Case

When

Count("calcltd.monthNumber") > Count("calcltd.monthName")

Then
CAST ('Increase' As String)

When

Count("calcltd.monthNumber") < Count("calcltd.monthName")

Then
'Decrease'

Else 'Almost Same'

End
As Measure

FROM "events-appdb-*"

group by "calcltd.monthName","calcltd.monthNumber"
"
| math "Measure"
| metric ""
metricFont={font align="center" color="#BBBBBB" family="'Open Sans', Helvetica, Arial, sans-serif" italic=false size=32 underline=false weight="bold"}
labelFont={font align="center" color="#000000" family="'Open Sans', Helvetica, Arial, sans-serif" italic=false size=14 underline=false weight="bold"}
| render

Ex; can i do something like this ??????
| essql
query="SELECT "calcltd.monthNumber",count(*) As CurrentMonth From Filter Group="Time",

"calcltd.monthName",Count(*) As PreviousMonth from Filter Group="Month",

Case

When

Count("calcltd.monthNumber") > Count("calcltd.monthName")

Then
CAST ('Increase' As String)

When

Count("calcltd.monthNumber") < Count("calcltd.monthName")

Then
'Decrease'

Else 'Almost Same'

End
As Measure

FROM "events-appdb-*"

group by "calcltd.monthName","calcltd.monthNumber"
"
| math "Measure"

[JLeysens (Jean-Louis Leysens)]

Hello @Solomon_k ! I'm not sure what you are describing is possible. If I'm understanding what you are trying to do correctly, you want to compare a metric from this month to the previous month and display something based on that.

Would it work for you to create a new "Metric" visualisation in Lens and use time shift to calculate the difference between this month and previous month. So:

1. Create a new metric vis
2. Select the field driving your metric
3. Edit the formula, try something like: average(bytes) - average(bytes, shift='1w')
4. Set up the colours you want for the value ranges

You then insert that vis into your canvas workpad.

[Solomon_k (solomon koujalgi)]

Hi JLeysens.
Thank you for replying, i need to Cast a String( "Increase"/"Decrease") as an output after comparing the records for the current and the previous month, which i will not be able to do it under the "Metric" visualization.

Is there any way i could possibly be able to assign FilterGroups to two different fields in the same query.
For Eg

query="SELECT "calcltd.monthNumber",count(*) As CurrentMonth use Filter Group="Time"( to fetch the time interval for "calcltd.monthNumber") ,

"calcltd.monthName",Count(*) As PreviousMonth use Filter Group="Month-01",
(to fetch the time interval for "calcltd.monthName)

???
This could possible solve my issue.

[JLeysens (Jean-Louis Leysens)]

You're trying to use a data source with different filters (as two data sources) in a single query and that is not possible in this context.

Does it need to be the text "increase" and "decrease"? You could do "green" for increase and "red" for decrease on a metric vis.