https://community.splunk.com/t5/Splunk-Search/Editing-current-finding-variance-search/m-p/651221#M225145 <P>Hello,</P><P>I have an search that is used on a dashboard that I would like tweaked.</P><P>Currently this search/panel displays the variance of current hour over the same hour the week before. for example: The value at hour 10 on Wed 7/19/23 will be compared to the value at hour 10 on Wed 7/12/23 and give variance.</P><P>Instead, I would like to compare current hour value to the value of the AVG of that same hour over the last 2 weeks (instead of compared to 1 day). For example I would like hour 10 on Wed 7/19/23 to be compared to the avg of hour 10 each day from Tues 7/18/23 to Wed 7/5/23.</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="bryhoffman_0-1689801500388.png" style="width: 400px;"><img src="https://community.splunk.com/t5/image/serverpage/image-id/26353iE395266881DE8AF1/image-size/medium?v=v2&amp;px=400" role="button" title="bryhoffman_0-1689801500388.png" alt="bryhoffman_0-1689801500388.png" /></span></P><P>&nbsp;</P><P>Current search:</P><P>| tstats count where index=msexchange host=SMEXCH13* earliest=-14d@d latest=-13d@d by _time span=1h<BR />| eval hour=strftime(_time,"%H")<BR />| eval ReportKey="2weekprior"<BR />| stats values(count) as count by hour, ReportKey<BR />| append<BR />[| tstats count where index=msexchange host=SMEXCH13* earliest=-7d@d latest=-6d@d by _time span=1h<BR />| eval hour=strftime(_time,"%H")<BR />| eval ReportKey="1weekprior"<BR />| stats values(count) as count by hour, ReportKey ]<BR />| append<BR />[| tstats count where index=msexchange host=SMEXCH13* earliest=-0d@d latest=-0h@h by _time span=1h<BR />| eval hour=strftime(_time,"%H")<BR />| eval ReportKey="currentweek"<BR />| stats values(count) as count by hour, ReportKey ]<BR />| eval currenthour=strftime(_time,"%H")<BR />| xyseries hour, ReportKey, count<BR />| eval nowhour = strftime(now(),"%H")<BR />| eval comparehour = nowhour-1<BR />|where hour&lt;=comparehour<BR />|sort by -hour<BR />| table hour,nowhour,comparehour, currentweek,1weekprior,2weekprior<BR />|eval 1weekvar = currentweek/'1weekprior'<BR />|eval 2weekvar = currentweek/'2weekprior'<BR />|eval variance=round(((('1weekvar'+'2weekvar')/2)*100)-100,2)<BR />|table hour,variance<BR />|head 5</P> Wed, 19 Jul 2023 21:18:53 GMT bryhoffman 2023-07-19T21:18:53Z https://community.splunk.com/t5/Splunk-Search/Editing-current-finding-variance-search/m-p/651221#M225145 <P>Hello,</P><P>I have an search that is used on a dashboard that I would like tweaked.</P><P>Currently this search/panel displays the variance of current hour over the same hour the week before. for example: The value at hour 10 on Wed 7/19/23 will be compared to the value at hour 10 on Wed 7/12/23 and give variance.</P><P>Instead, I would like to compare current hour value to the value of the AVG of that same hour over the last 2 weeks (instead of compared to 1 day). For example I would like hour 10 on Wed 7/19/23 to be compared to the avg of hour 10 each day from Tues 7/18/23 to Wed 7/5/23.</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="bryhoffman_0-1689801500388.png" style="width: 400px;"><img src="https://community.splunk.com/t5/image/serverpage/image-id/26353iE395266881DE8AF1/image-size/medium?v=v2&amp;px=400" role="button" title="bryhoffman_0-1689801500388.png" alt="bryhoffman_0-1689801500388.png" /></span></P><P>&nbsp;</P><P>Current search:</P><P>| tstats count where index=msexchange host=SMEXCH13* earliest=-14d@d latest=-13d@d by _time span=1h<BR />| eval hour=strftime(_time,"%H")<BR />| eval ReportKey="2weekprior"<BR />| stats values(count) as count by hour, ReportKey<BR />| append<BR />[| tstats count where index=msexchange host=SMEXCH13* earliest=-7d@d latest=-6d@d by _time span=1h<BR />| eval hour=strftime(_time,"%H")<BR />| eval ReportKey="1weekprior"<BR />| stats values(count) as count by hour, ReportKey ]<BR />| append<BR />[| tstats count where index=msexchange host=SMEXCH13* earliest=-0d@d latest=-0h@h by _time span=1h<BR />| eval hour=strftime(_time,"%H")<BR />| eval ReportKey="currentweek"<BR />| stats values(count) as count by hour, ReportKey ]<BR />| eval currenthour=strftime(_time,"%H")<BR />| xyseries hour, ReportKey, count<BR />| eval nowhour = strftime(now(),"%H")<BR />| eval comparehour = nowhour-1<BR />|where hour&lt;=comparehour<BR />|sort by -hour<BR />| table hour,nowhour,comparehour, currentweek,1weekprior,2weekprior<BR />|eval 1weekvar = currentweek/'1weekprior'<BR />|eval 2weekvar = currentweek/'2weekprior'<BR />|eval variance=round(((('1weekvar'+'2weekvar')/2)*100)-100,2)<BR />|table hour,variance<BR />|head 5</P> Wed, 19 Jul 2023 21:18:53 GMT https://community.splunk.com/t5/Splunk-Search/Editing-current-finding-variance-search/m-p/651221#M225145 bryhoffman 2023-07-19T21:18:53Z https://community.splunk.com/t5/Splunk-Search/Editing-current-finding-variance-search/m-p/651261#M225164 <P>The search is a lot simpler if we just follow your description of the problem:</P><LI-CODE lang="markup">| tstats count where index=msexchange host=SMEXCH13* earliest=-2w@d latest=-0h@h by _time span=1h | eval hour = strftime(_time,"%H") | eval interval = if(_time &gt; relative_time(now(), "-0d@d"), "today", "past2weeks") | stats avg(count) as count by hour interval | xyseries hour interval count | where isnotnull(today) | eval variance = round((today / past2weeks - 1) * 100, 2) | fields - count interval</LI-CODE><P>Note:</P><OL><LI>The past2weeks calculation does not include today's. &nbsp;In most cases, this is acceptable but today's can be included if necessary.</LI><LI>The "variance" calculation is based on your description and the sample code you provided, namely, the percentage difference between today's value and the mean value in the past two weeks. &nbsp;This is not the common mathematical definition of variance; and the value can be negative.</LI></OL> Thu, 20 Jul 2023 08:59:54 GMT https://community.splunk.com/t5/Splunk-Search/Editing-current-finding-variance-search/m-p/651261#M225164 yuanliu 2023-07-20T08:59:54Z