# Kibana Canvas: Comparing current month Event counts to previous month counts and result to be in a form of string "increase" or "Decrease"
**URL:** https://discuss.elastic.co/t/kibana-canvas-comparing-current-month-event-counts-to-previous-month-counts-and-result-to-be-in-a-form-of-string-increase-or-decrease/311641
**Category:** Kibana
**Tags:** canvas
**Created:** [August 8, 2022, 10:54am UTC](https://discuss.elastic.co/t/kibana-canvas-comparing-current-month-event-counts-to-previous-month-counts-and-result-to-be-in-a-form-of-string-increase-or-decrease/311641 "2022-08-08T10:54:59Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@Solomon\_k](https://discuss.elastic.co/u/Solomon_k)
#### Post date: [August 8, 2022, 10:54am UTC](https://discuss.elastic.co/t/kibana-canvas-comparing-current-month-event-counts-to-previous-month-counts-and-result-to-be-in-a-form-of-string-increase-or-decrease/311641/1 "2022-08-08T10:54:59Z")
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"
---
### Author:  [@JLeysens](https://discuss.elastic.co/u/JLeysens)
#### Post date: [August 9, 2022, 8:53am UTC](https://discuss.elastic.co/t/kibana-canvas-comparing-current-month-event-counts-to-previous-month-counts-and-result-to-be-in-a-form-of-string-increase-or-decrease/311641/2 "2022-08-09T08:53:44Z")
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.
---
### Author:  [@Solomon\_k](https://discuss.elastic.co/u/Solomon_k)
#### Post date: [August 9, 2022, 9:58am UTC](https://discuss.elastic.co/t/kibana-canvas-comparing-current-month-event-counts-to-previous-month-counts-and-result-to-be-in-a-form-of-string-increase-or-decrease/311641/3 "2022-08-09T09:58:25Z")
Hi [JLeysens](https://discuss.elastic.co/u/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.
---
### Author:  [@JLeysens](https://discuss.elastic.co/u/JLeysens)
#### Post date: [August 9, 2022, 10:49am UTC](https://discuss.elastic.co/t/kibana-canvas-comparing-current-month-event-counts-to-previous-month-counts-and-result-to-be-in-a-form-of-string-increase-or-decrease/311641/4 "2022-08-09T10:49:58Z")
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.
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [September 6, 2022, 10:50am UTC](https://discuss.elastic.co/t/kibana-canvas-comparing-current-month-event-counts-to-previous-month-counts-and-result-to-be-in-a-form-of-string-increase-or-decrease/311641/5 "2022-09-06T10:50:20Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.