https://community.splunk.com/t5/Splunk-Search/Count-distinct-SPL-help/m-p/527498#M148904 <P>Great ideal ITWhisperer,<BR />It is exactly what i am looking for. I am developing a sum count between date, but your solution is straightforward and much better. Hope to see you arround very soon</P> Mon, 02 Nov 2020 10:51:21 GMT thuhuongle 2020-11-02T10:51:21Z https://community.splunk.com/t5/Splunk-Search/Count-distinct-SPL-help/m-p/527316#M148859 <P>Hi,&nbsp;<BR />Looking forward to learn from you guys. I am stucked at this calculation: Total of product in contract.<BR />I made a simple dataset to simplify my data.&nbsp;</P><P>&nbsp;</P><LI-CODE lang="markup">| makeresults | eval date = "2017-01-30" , source = "a", id="111" | makemv delim="," id | makemv delim="," date | makemv delim="," source | append [| makeresults | eval date = "2017-01-30" , source = "b", id="222" | makemv delim="," id | makemv delim="," date | makemv delim="," source] | append [| makeresults | eval date = "2019-08-20" , source = "a", id="333" | makemv delim="," id | makemv delim="," date | makemv delim="," source] | append [| makeresults | eval date = "2020-01-20" , source = "a", id="444" | makemv delim="," id | makemv delim="," date | makemv delim="," source] | append [| makeresults | eval date = "2020-03-20" , source = "b", id="555" | makemv delim="," id | makemv delim="," date | makemv delim="," source]</LI-CODE><P>&nbsp;</P><P>INPUT: let's image _time is date time of buying record</P><TABLE border="1" width="100%"><TBODY><TR><TD width="25%" height="25px">_time</TD><TD width="25%" height="25px">signed contract date</TD><TD width="25%" height="25px">id</TD><TD width="25%" height="25px">source</TD></TR><TR><TD width="25%" height="25px">2020-10-30 14:55:55</TD><TD width="25%" height="25px">2017-01-30</TD><TD width="25%" height="25px">111</TD><TD width="25%" height="25px">a</TD></TR><TR><TD width="25%" height="25px">2020-10-30 14:55:55</TD><TD width="25%" height="25px">2017-01-30</TD><TD width="25%" height="25px">222</TD><TD width="25%" height="25px">b</TD></TR><TR><TD width="25%" height="25px">2020-08-30 14:55:55</TD><TD width="25%" height="25px">2019-08-20</TD><TD width="25%" height="25px">333</TD><TD width="25%" height="25px">a</TD></TR><TR><TD width="25%" height="25px">2020-01-30 14:55:55</TD><TD width="25%" height="25px">2020-01-20</TD><TD width="25%" height="25px">444</TD><TD width="25%" height="25px">a</TD></TR><TR><TD width="25%" height="25px">2020-09-30 14:55:55</TD><TD width="25%" height="25px">&nbsp;2020-03-20</TD><TD width="25%" height="25px">555</TD><TD width="25%" height="25px">b</TD></TR></TBODY></TABLE><P><BR />The expected output: Count total product in contract from 12/2019 to 03/2020</P><TABLE border="1" width="100%"><TBODY><TR><TD width="33.333333333333336%" height="25px">Time</TD><TD width="33.333333333333336%" height="25px">total_nb_product</TD><TD width="33.333333333333336%" height="25px">Source</TD></TR><TR><TD width="33.333333333333336%" height="25px">12/2019</TD><TD width="33.333333333333336%" height="25px">2</TD><TD width="33.333333333333336%" height="25px">a</TD></TR><TR><TD width="33.333333333333336%" height="25px">12/2019</TD><TD width="33.333333333333336%" height="25px">1</TD><TD width="33.333333333333336%" height="25px">b</TD></TR><TR><TD width="33.333333333333336%" height="25px">01/2020</TD><TD width="33.333333333333336%" height="25px">3</TD><TD width="33.333333333333336%" height="25px">a</TD></TR><TR><TD width="33.333333333333336%" height="25px">01/2020</TD><TD width="33.333333333333336%" height="25px">1</TD><TD width="33.333333333333336%" height="25px">b</TD></TR><TR><TD width="33.333333333333336%" height="25px">02/2020</TD><TD width="33.333333333333336%" height="25px">3</TD><TD width="33.333333333333336%" height="25px">a</TD></TR><TR><TD width="33.333333333333336%" height="25px">02/2020</TD><TD width="33.333333333333336%" height="25px">1</TD><TD width="33.333333333333336%" height="25px">b</TD></TR><TR><TD width="33.333333333333336%" height="25px">03/2020</TD><TD width="33.333333333333336%" height="25px">3</TD><TD width="33.333333333333336%" height="25px">a</TD></TR><TR><TD width="33.333333333333336%" height="25px">03/2020</TD><TD width="33.333333333333336%" height="25px">2</TD><TD width="33.333333333333336%" height="25px">b</TD></TR></TBODY></TABLE><P>Thank for your time and hope to received your suggestion.&nbsp;<BR />Great weekend</P> Fri, 30 Oct 2020 14:03:39 GMT https://community.splunk.com/t5/Splunk-Search/Count-distinct-SPL-help/m-p/527316#M148859 thuhuongle 2020-10-30T14:03:39Z https://community.splunk.com/t5/Splunk-Search/Count-distinct-SPL-help/m-p/527339#M148864 <LI-CODE lang="markup">| makeresults | eval date = "2017-01-30" , source = "a", id="111" | makemv delim="," id | makemv delim="," date | makemv delim="," source | append [| makeresults | eval date = "2017-01-30" , source = "b", id="222" | makemv delim="," id | makemv delim="," date | makemv delim="," source] | append [| makeresults | eval date = "2019-08-20" , source = "a", id="333" | makemv delim="," id | makemv delim="," date | makemv delim="," source] | append [| makeresults | eval date = "2020-01-20" , source = "a", id="444" | makemv delim="," id | makemv delim="," date | makemv delim="," source] | append [| makeresults | eval date = "2020-03-20" , source = "b", id="555" | makemv delim="," id | makemv delim="," date | makemv delim="," source] | eval Time=round(relative_time(strptime(date,"%Y-%m-%d"),"@mon")) | makecontinuous Time span=1mon | fields - _time date | eventstats values(source) as sources | mvexpand sources | eval source=coalesce(source, sources) | eval id=if(source=sources,id,null) | fields - source | rename sources as Source | stats values(id) as id by Time Source | streamstats count(id) as total_nb_product by Source | fields Time total_nb_product Source | fieldformat Time=strftime(Time,"%m/%Y")</LI-CODE> Fri, 30 Oct 2020 16:44:18 GMT https://community.splunk.com/t5/Splunk-Search/Count-distinct-SPL-help/m-p/527339#M148864 ITWhisperer 2020-10-30T16:44:18Z https://community.splunk.com/t5/Splunk-Search/Count-distinct-SPL-help/m-p/527498#M148904 <P>Great ideal ITWhisperer,<BR />It is exactly what i am looking for. I am developing a sum count between date, but your solution is straightforward and much better. Hope to see you arround very soon</P> Mon, 02 Nov 2020 10:51:21 GMT https://community.splunk.com/t5/Splunk-Search/Count-distinct-SPL-help/m-p/527498#M148904 thuhuongle 2020-11-02T10:51:21Z