Archive
Highlighted

in Panel result getting unwanted "NaN" in search query getting correct result.

New Member

in my Splunk query result getting result as "NaN" but i will run my query in search i will get correct result not NaN Please help me regarding this.

Tags (1)
0 Karma
Highlighted

Re: in Panel result getting unwanted "NaN" in search query getting correct result.

Legend

@anjneesharma what is your query, which visualization are you using and can you post some sample data?

NaN could be because of several reasons, for example... if you mess with the time format and try to plot a chart with it you would get an error in chart while stats will show you HH:MM:SS time with count. Following is a run anywhere search.

|  makeresults
|  eval _time=strftime(_time,"%H:%M:%S")
|  eval count=5
|  chart max(count) over _time



| eval message="Happy Splunking!!!"


0 Karma
Highlighted

Re: in Panel result getting unwanted "NaN" in search query getting correct result.

New Member

@niketnilay plz find my query bellow

|savedsearch copmonthlysummaryassavedsearch|stats sum(fycciprev) as fycciprev sum(fynrprev) as fynrprev sum(rytdnrcurr) as rytdnrcurr sum(ytdccicurr) as ytdccicurr sum(ytdcciprev) as ytdcciprev sum(ytdnrprev) as ytdnrprev by ou| eval fiscalyprev = round(fycciprev/fynrprev *100,1) | eval yeartdcurr = round(ytdccicurr/rytdnrcurr *100,1) | eval yeartdprev = round(ytdcciprev/ytdnrprev 100,1) | fields - fy ytd* | eval ytdvsprev = yeartdcurr-yeartdprev|eval rytdnrcurr = round(rytdnrcurr,1) | eval rytdnrcurr = tostring(rytdnrcurr, "commas")| search ou="PRD AAPAC OU" | table ou fiscalyprev rytdnrcurr yeartdcurr yeartdprev ytdvsprev |rename fiscalyprev as "AS Full Prev FY CCI%" rytdnrcurr as "AS YTD actuals FY18 NR" yeartdcurr as "AS YTD actuals FY18 CCI%" yeartdprev as "AS YTD Prev FY CCI%" ytdvsprev as "AS YTD FY18 CCI% actuals vs AS YTD Prev FY CCI%" contract_number as "Contract" ou as "Primary"

Here ou as "Primary" is giving result as "NaN"

Please help me regarding this

0 Karma
Highlighted

Re: in Panel result getting unwanted "NaN" in search query getting correct result.

Legend

You seem to have string data in ou based on your search query. So, this is indeed non-numeric data.

search ou="PRD AAPAC OU"

If you output the result in Table there should be no issues.

Also while posting code or sample data on Splunk Answers use to code button i.e. 101010 or shortcut Ctrl+K

| savedsearch cop_monthly_summary_as_savedsearch 
| stats sum(fy_cci_prev) as fy_cci_prev sum(fy_nr_prev) as fy_nr_prev sum(r_ytd_nr_curr) as r_ytd_nr_curr sum(ytd_cci_curr) as ytd_cci_curr sum(ytd_cci_prev) as ytd_cci_prev sum(ytd_nr_prev) as ytd_nr_prev by ou 
| eval fiscaly_prev = round(fy_cci_prev/fy_nr_prev*100,1) 
| eval yeartd_curr = round(ytd_cci_curr/r_ytd_nr_curr*100,1) 
| eval yeartd_prev = round(ytd_cci_prev/ytd_nr_prev*100,1) 
| fields - fy ytd 
| eval ytd_vs_prev = yeartd_curr-yeartd_prev 
| eval r_ytd_nr_curr = round(r_ytd_nr_curr,1) 
| eval r_ytd_nr_curr = tostring(r_ytd_nr_curr, "commas") 
| search ou="PRD AAPAC OU" 
| table ou fiscaly_prev r_ytd_nr_curr yeartd_curr yeartd_prev ytd_vs_prev 
| rename fiscaly_prev as "AS Full Prev FY CCI%" r_ytd_nr_curr as "AS YTD actuals FY18 NR" yeartd_curr as "AS YTD actuals FY18 CCI%" yeartd_prev as "AS YTD Prev FY CCI%" ytd_vs_prev as "AS YTD FY18 CCI% actuals vs AS YTD Prev FY CCI%" contract_number as "Contract" ou as "Primary"



| eval message="Happy Splunking!!!"


0 Karma
Highlighted

Re: in Panel result getting unwanted "NaN" in search query getting correct result.

New Member

@niketnilay

If i will open my same in query in search result is coming correct i.e insead of NAN real value comes.

0 Karma
Highlighted

Re: in Panel result getting unwanted "NaN" in search query getting correct result.

Legend

How are you presenting the results? Table or any other visualization?

Can you post the simple XML from the panel?

Also what is the query in saved search...cop_monthly_summary_as_savedsearch




| eval message="Happy Splunking!!!"


0 Karma
Highlighted

Re: in Panel result getting unwanted "NaN" in search query getting correct result.

New Member

Hi Niket, with respect to the above Query i am continuing where Anjnee had left.

Below is the sample xml for that particular panel:

    <panel>
      <table>
        <search id="monthly_summary" ref="cop_monthly_summary_savedsearch"></search>
      </table>
    </panel>

<row depends="$ou_level$">
    <panel>
      <table id="poc_monthly2">
        <title>$sgccr_year$ YTD COP contract performance – Application Services - Summary at OU (as of $sgccr_month$ YTD)</title>
        <search base="target_master">
          <query>| table geography og ou   contract_number contract_name primary_csg master_client_name |join contract_number [|savedsearch cop_sgccr_sg_savedsearch | where service_group="APPLICATION OUTSOURCING" OR service_group="SYSTEM INTEGRATION" | stats sum(ytd_nr) as r_ytd_nr_curr sum(ytd_cci) as ytd_cci_curr by contract_number | fillnull value=0 ]| join contract_number type=outer [|savedsearch cop_previousyr_sgccr_sg_savedsearch | where service_group="APPLICATION OUTSOURCING" OR service_group="SYSTEM INTEGRATION" | stats sum(q1_nr) AS "q1_nr" sum(q1_cci) AS "q1_cci" sum(q2_nr) AS "q2_nr" sum(q2_cci) AS "q2_cci" sum(q3_nr) AS "q3_nr" sum(q3_cci) AS "q3_cci" sum(q4_nr) AS "q4_nr" sum(q4_cci) AS "q4_cci" by contract_number | fillnull value=0 | eval q1_cci=if(isnull(q1_cci),0,q1_cci) |eval q2_cci=if(isnull(q2_cci),0,q2_cci)| eval q3_cci=if(isnull(q3_cci),0,q3_cci)| eval q4_cci=if(isnull(q4_cci),0,q4_cci)| eval q1_nr=if(isnull(q1_nr),0,q1_nr) |eval q2_nr=if(isnull(q2_nr),0,q2_nr)| eval q3_nr=if(isnull(q3_nr),0,q3_nr)| eval q4_nr=if(isnull(q4_nr),0,q4_nr) | eval fy_cci_prev = q1_cci + q2_cci + q3_cci + q4_cci | eval fy_nr_prev = q1_nr + q2_nr + q3_nr + q4_nr | eval forecast_month="1"|eval current_quarter_no=case(forecast_month=9 OR forecast_month=10 OR forecast_month=11,1,forecast_month=12 OR forecast_month=1 OR forecast_month=2,2,forecast_month=3 OR forecast_month=4 OR forecast_month=5,3,forecast_month=6 OR forecast_month=7 OR forecast_month=8,4) | eval ytd_nr_prev=case(current_quarter_no=1,q1_nr,current_quarter_no=2,q1_nr+q2_nr,current_quarter_no=3,q1_nr+q2_nr+q3_nr,current_quarter_no=4,q1_nr+q2_nr+q3_nr+q4_nr) | eval ytd_cci_prev=case(current_quarter_no=1,q1_cci,current_quarter_no=2,q1_cci+q2_cci,current_quarter_no=3,q1_cci+q2_cci+q3_cci,current_quarter_no=4,q1_cci+q2_cci+q3_cci+q4_cci)| fields - current_quarter_no forecast_month  | fields - q* yearofpublish_sgccr ytd_nr ytd_cci] |stats sum(fy_cci_prev) as fy_cci_prev sum(fy_nr_prev) as fy_nr_prev sum(r_ytd_nr_curr) as r_ytd_nr_curr sum(ytd_cci_curr) as ytd_cci_curr sum(ytd_cci_prev) as ytd_cci_prev sum(ytd_nr_prev) as ytd_nr_prev by ou| eval fiscaly_prev = round(fy_cci_prev/fy_nr_prev *100,1) | eval yeartd_curr = round(ytd_cci_curr/r_ytd_nr_curr *100,1) | eval yeartd_prev = round(ytd_cci_prev/ytd_nr_prev *100,1) | fields - fy* ytd* |  eval ytd_vs_prev = yeartd_curr-yeartd_prev|eval r_ytd_nr_curr = round(r_ytd_nr_curr,0)  | eval r_ytd_nr_curr = tostring(r_ytd_nr_curr, "commas")|search ou="$primary_ou$"|table ou fiscaly_prev r_ytd_nr_curr yeartd_curr yeartd_prev ytd_vs_prev |rename fiscaly_prev as "AS Full Prev FY CCI%" r_ytd_nr_curr as "AS YTD actuals FY18 NR" yeartd_curr as "AS YTD actuals FY18 CCI%" yeartd_prev as "AS YTD Prev FY CCI%" ytd_vs_prev as "AS YTD FY18 CCI% actuals vs AS YTD Prev FY CCI%" contract_number as "Contract" ou as "Primary OU"</query>
          <earliest>0</earliest>
          <latest></latest>
        </search>
        <option name="wrap">true</option>
        <option name="rowNumbers">false</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">cell</option>
        <option name="count">10</option>
      </table>
    </panel>
  </row>

The savedsearch had the below code:

| pivot cop_datapack Target_Master_Control_List latest(month) AS "month" latest(Status) AS "Status" latest(contract_name) AS "contract_name" latest(cop_owner_name) AS "cop_owner_name" latest(ao) AS "ao" latest(bpo) AS "bpo" latest(cop_wave) AS "cop_wave" latest(engagement_id) AS "engagement_id" latest(engagement_name) AS "engagement_name" latest(hri_list_status) AS "hri_list_status" latest(ic) AS "ic" latest(io) AS "io" latest(og) AS "og" latest(master_client_name) AS "master_client_name" latest(master_contract_name) AS "master_contract_name" latest(mc) AS "mc" latest(opera_contract) AS "opera_contract" latest(primary_csg) AS "primary_csg" latest(revised_rbe) AS "revised_rbe" latest(sc) AS "sc" latest(si) AS "si" latest(tc) AS "tc" latest(OU) AS "ou" latest(delivery_coach) AS "delivery_coach" latest(delivery_lead_name) AS "delivery_lead_name" latest(target_cci) as target_cci SPLITROW geography AS geography SPLITROW contract_number AS contract_number|where Status="Active"
0 Karma