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!

#### What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

#### Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

#### Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...