Reporting

## Help with streamstats count with previous sum in case of no value

Path Finder

Hi,

I am using streamstats to calculate the rank based on cumulative count per day per category. On few days, a particular category may not appear. So, on those days, I want to have the count for that category from the  previous day. I tried all the arguments of streamstats and other commands with no success. Can someone help me on it please?

I am pasting the code of similar case but for ranking based on the cumulative points for each football match

``````index=index
| stats sum(TotalPoints) AS Points BY match, "Sold To"
| fillnull value=0
| rename "Sold To" AS Owner
| sort match
| streamstats sum(Points) AS Total BY Owner
| sort match Total
| streamstats count AS Rank BY match
| xyseries match Owner Rank``````

Output

 match Owner1 Owner2 Owner3 1 2 (Rank 2, Total: 10) 1 (Rank 1, Total: 15) (Total: 0) 2 1 (Rank 1, Total: 25) 3 (Rank 3, Total: 18) 2 (Rank 2, Total: 20) 3 (Total: 0) 2 (Rank 2, Total: 23) 1 (Rank 1, Total: 30)

Expected Output

 match Owner1 Owner2 Owner3 1 2 (Rank 2, Total: 10) 1 (Rank 1, Total: 15) 3 (Rank 3, Total: 0) 2 1 (Rank 1, Total: 25) 3 (Rank 3, Total: 18) 2 (Rank 2, Total: 20) 3 2 (Rank 3, Total: 25) 3 (Rank 2, Total: 23) 1 (Rank 1, Total: 30)
Labels (1)
• ### other

Tags (1)
1 Solution
SplunkTrust

Your first stats should be a chart command, because stats doesn't give you the null entries you are expecting. You then need to untable the chart after the fillnull to get the stats you were expecting

``````index=index
| chart sum(TotalPoints) AS Points BY match, "Sold To"
| fillnull value=0
| untable match Owner Points
| sort match
| streamstats sum(Points) AS Total BY Owner
| sort match Total
| streamstats count AS Rank BY match
| xyseries match Owner Rank``````
SplunkTrust

Your first stats should be a chart command, because stats doesn't give you the null entries you are expecting. You then need to untable the chart after the fillnull to get the stats you were expecting

``````index=index
| chart sum(TotalPoints) AS Points BY match, "Sold To"
| fillnull value=0
| untable match Owner Points
| sort match
| streamstats sum(Points) AS Total BY Owner
| sort match Total
| streamstats count AS Rank BY match
| xyseries match Owner Rank``````
Path Finder

Thank you. It worked as expected

SplunkTrust

You can either modify the intermediate results.

`[...]| streamstats count AS Rank by match| streamstats current=f window=1 last(Rank) AS prevrank by match| eval Rank=if(Rank=0,prevrank,Rank)| xyseries [...]`

Or you can modify the final table. But it needs a "matchable" set of fields since you can't match negatively with foreach and you can't (at least I don't know of any way to do so) check field's name in eval.

`[...]| foreach Owner*   [ <<FIELD>>=if(<<FIELD>> > 0, <<FIELD>>, null()) ]| filldown Owner*| fillnull value=0 Owner*`

But I'd go with the first one (the streamstats)

Get Updates on the Splunk Community!

#### Cloud Platform | Customer Change Announcement: Email Notification Will Be Available ...

The Notification Team is migrating our email service provider since currently there’s no support ...

#### Mastering Synthetic Browser Testing: Pro Tips to Keep Your Web App Running Smoothly

To start, if you're new to synthetic monitoring, I recommend exploring this synthetic monitoring overview. In ...

#### Splunk Edge Processor | Popular Use Cases to Get Started with Edge Processor

Splunk Edge Processor offers more efficient, flexible data transformation – helping you reduce noise, control ...