How to count event by chosen month

[phamxuantung]

Hi, I have a list of events span across more than a year, the event will contain type of card, transaction status. I want to have a table with a drop down box for user to choose month and count the event by month, the month before, status, type of card, and finally caculate the rate between them. For example, if the users  choose April, then MONTH-1 will be March, and the table will br like this:

CARD|STATUS|MONTH|MONTH-1|RATE
VISA|1     |3    |6      |100%
VISA|0     |8    |4      |50%
MC  |99    |5    |9      |90%         

I then encounter 2 problem:

1. I try to test out by simple display them all by using stats

index=index
|stats count by date_month date_year STATUS CARD

but it don't display [CARD|STATUS|date_month|count] like I thought it would be, it blank, it still show if I only use date_month or don't use it at all.

2. I don't know how to stats count by in two seperate months, I could display them all and then search by using token, but then I won't br able to show the month before side by side and then caculate them. Then there's also problem with different year, and 01/2022 and 12/2021.

If anyone know the solution for these problems I'll be very appriciate.

Thank you in advance.

[ITWhisperer]

Does this help:

index=index
| bin span=1mon _time
| stats count by _time STATUS CARD
| where _time=relative_time($chosen_month$,"-1mon@mon") OR _time=relative_time($chosen_month$,"@mon")

This assumes you have $chosen_month$ in epoch format. If not, you can create this in the change handler of your dropdown.

[isoutamo]

Can you give some scrambled sample (raw) data to us?