How do I display results grouped by two field values and then display a count over a 7 day period with span=1d

[thomasaju]

So I have a data set and with some splunk magic, I was able to display the results in the following format:
query:

..... | stats count by error, state | sort count | chart list(error) as error, list(count) as count by state

Results:

    State   error           Count
    -----   -----           -----
    CA     21102           69
             42112           32
             10551           45
             81092           15
             10453           18
    VA     21102           18
             42112           10
             10551           16
             81092           19
             10453           12
    WA     21102           17
             42112           11
             81092           31
             10453           10

What I would like to see is the count over last 7 days which would give me the results like below:

    State   error           06/24        06/25       06/26        06/27
    -----   -----           -----        -----       -----        -----
    CA     21102           11           19           21           21
             42112           11           12           12           15
             10551           11           12           14           17
             81092           16           13           15           19
             10453           11           17           18           11
    VA     21102           11           19           21           21
             42112           11           12           12           15
             10551           11           12           14           17
             81092           16           13           15           19
             10453           11           17           18           11
    WA     21102           11           19           21           21
             42112           11           12           12           15
             81092           16           13           15           19
             10453           11           17           18           11

Basically splitting the count by date.

[sundareshr · Accepted solution]

See if this gives you what you're looking for

..... | eval stateerror=state."#".error | bin span=1d _time as time | eval time=strftime(time, "%,/%d") | chart count over stateerror by time | rex field=stateerror "(?<State>[^#]+)#(?<Error>\d+)" | fields -stateerror

[thomasaju]

thanks @sundareshr. The query does give me the results like below:

        State   error           06/24        06/25       06/26        06/27
        -----   -----           -----        -----       -----        -----
        CA     21102           11           19           21           21
        CA       42112           11           12           12           15
        CA       10551           11           12           14           17
        CA       81092           16           13           15           19
        CA       10453           11           17           18           11
        VA     21102           11           19           21           21
        VA       42112           11           12           12           15
        VA       10551           11           12           14           17
        VA       81092           16           13           15           19
        VA       10453           11           17           18           11
        WA     21102           11           19           21           21
        WA       42112           11           12           12           15
        WA       81092           16           13           15           19
        WA       10453           11           17           18           11

But it's not grouping the state as I've shown in my original post. Is it possible that I can group the state values together?

[sundareshr]

Add this to the end

| stats values(*) as * by State

[thomasaju]

This works. Though I ended up using. Thanks for the help, @sundareshr

| stats list(*) as * by State