Extract Day, Month and year from Today using painless script

[hiteshmakky (HMak)]

Hi Friends,
I am new to the EKL stack. I am trying to calculate MTD sales from data set.
We have three columns

1. Product Code
2. Date (dd-MM-yyyy format)
3. Sales
   All I am trying to do is calculate MTD sales for e.g. If today is 04th of Aug 2021. The code should be able to filter the records from 01st Aug 2021 to 04th Aug 2021 and sum the Sales column. The date which do not fall into the filter condition of MTD should not be considered for the MTD calculation.

|Product_Code|Date|Sales|
|P1|02-08-2021|55000|
|P1|03-08-2021|55000|
|P2|01-08-2021|15842|
|P3|04-08-2021|14892|
|P4|20-10-2020|59000|
|P4|30-10-2020|59000|
|P5|21-04-2021|45000|
|P6|10-06-2021|0|
|P7|08-02-2021|86771|
|P8|22-01-2021|7295|
|P8|21-01-2022|7295|
|P9|22-06-2021|47362|
|P9|24-06-2022|47362|

[bhavyarm]

Hello,

Two questions for you. What is MTD sales?
Which chart types did you try? If you look at lens and data table?

For example - here I am looking at logs and bytes. I made a data table for timestamp and bytes sum in lens. And I can look at different time period by using the time picker on top.

Kibana Lens

Does that help?

Thanks
Bhavya

[hiteshmakky (HMak)]

Hi Bhavya,

MTD stand for Month to Date. This is a common mertic used for performance monitoring dashboard.

For e.g. If today is 28th July 2021, then the MTD sales metric should give sales from 01st of July 2021 till 28th of July 2021
If today is 15th Aug 2021, then the MTD sales metric should give sales from 01st of Aug 2021 till 15th of Aug 2021.

MTD metric always starts from 01st day of a month of today till today.

Let write a sudo code for reference:
Today (system date ) = 28th Jul 2021
MTD Sales = Sum (All the sales that has occured between 01st of July 2021 till 28th July 2021 inclusing both the start date and end)

Also a pointer, I want to derive the MTD calucation using painless script

Hope this gives better clairfication