How to add/force line chart to show all the months of the year even if there are zero values for certain months

[motaghis]

Hello.  I'm having a bit of an issue that I cant' figure out.  I have a query that references an inputlookup and produces a line chart. However, it only shows the dates for which there are values.   How do I modify this query to be able to show a complete line chart where all months of the year show and to force a value to equal 100 even if there is no value for a particular field.  Please help.  Thank you.

| inputlookup something.csv
| search business_service IN('"SomeService"') the_month="2020-*"
| stats sum(imp_duration) as YTD_Impact_Hours values(inc_no) as inc_no by business_service the_month
| eval YTD_Hours=6600
| eval Score=round(((1-(YTD_Impact_Hours / YTD_Hours))*100),3)
| eval expected_score=100
| stats values(expected_score) as "Expected Score" values(Score) as Score sum(YTD_Impact_Hours) as YTD_Impact_Hours by the_month inc_no
| eval the_month=the_month."-01"
| eval the_month=strptime(the_month, "%Y-%m-%d")
| sort 14 -the_month | sort the_month
| convert timeformat="%b-%y" ctime(the_month)
| rename business_service as "Business Service" YTD_Impact_Hours as "Impact Hours" the_month as Date inc_no as Incident Score as "Actual Score"
| fields - "Impact Hours"
| fillnull "Actual Score" value=100

[thambisetty · Accepted solution]

Did you try this 

https://community.splunk.com/t5/Deployment-Architecture/How-to-produce-empty-time-buckets/m-p/172323

[motaghis]

I was able to use the appendpipe from the link @thambisetty provided to get what I need. Thank you! 

| appendpipe [|stats count | addinfo | eval temp=info_min_time."##".info_max_time | makemv temp delim="##" | mvexpand temp | eval count=0 | eval _time=temp | table _time count] | fillnull value=100

| timechart span=1d values(Score) as Score by business_service | makecontinuous | fillnull value=100

[motaghis]

Thank you @thambisetty for your reply.  I made a few modification to a field I found a in the inputlookup called "opened" and performed and | eval _time to make it work with timechart.  However,  the timechart ends on May instead of the current month of Oct.   How do I get the timechart to start from the begining of the year to now.

Added the following:

| eval _time = strptime(opened, "%Y-%m-%d")
| timechart span=1d list(Score) as Score by business_service | makecontinuous | fillnull value=100

Full Query with change.  I attached a screenshot of what it looks like.:

| inputlookup "Something.csv"
| eval _time = strptime(opened, "%Y-%m-%d")
| search business_service IN('"foo"')
| stats sum(imp_duration) as YTD_Impact_Hours values(inc_no) as inc_no by business_service _time
| eval YTD_Hours=6600
| eval Score=round(((1-(YTD_Impact_Hours / YTD_Hours))*100),3)
| timechart span=1d list(Score) as Score by business_service | makecontinuous | fillnull value=100
| eval expected_score=100
| rename business_service as "Business Service" YTD_Impact_Hours as "Impact Hours" the_month as Date inc_no as Incident Score as "Actual Score"
| fields - "Impact Hours" Incident