Overlaying on chart with a previous years data when x-axis do not match

[jackreeves]

I am displaying some data by Month for 2018/2019 (i.e. 01-2018, 02-2018) on a barchart.

Search Query:
( sourcetype=sourcetype1) OR (sourcetype=sourcetype2) OR (sourcetype=sourcetype3)
| chart sum(eval(if(sourcetype="sourcetype1",ICOS,NULL))) as Actuals sum(eval(if(sourcetype="sourcetype2",ICOS,NULL))) as Forecast sum(eval(if(sourcetype="sourcetype3",ICOS,NULL))) as Budget over "Month"

However I also want to be able to overlay 2017 data over the same period without changing the above x-axis of Month. The 2017 data will come from sourcetype1.

Any ideas how I could do that?

[DalJeanis]

Please get in the habit of using ISO format for dates. In this case, "2018-01", so they will naturally sort in the correct order without any extra work. Here's one way to accomplish your request. This assumes that there is a _time field in epoch format that establishes the month. If you need to reformat that information, then adjust the code as needed.

 ( sourcetype=sourcetype1) OR (sourcetype=sourcetype2) OR (sourcetype=sourcetype3)

 | rename COMMENT as "Flag the budget type and the value to be summed up"
 | eval myType=case(sourcetype="sourcetype1" AND some test for current year,"Actuals"
                    sourcetype="sourcetype1" AND some test for prior year,"PriorYear",
                    sourcetype="sourcetype2","Forecast",
                    sourcetype="sourcetype3", "Budget")
 | eval myValue = if(myType="PriorYear", whatever the field is for prior years actual, ICOS)

 | rename COMMENT as "Use the end of the report time range to determine the beginning of the year"
 | rename COMMENT as "Advance prior year data by one years, and throw away any future data"
 | addinfo
 | eval _time = if(_time<relative_time(info_max_time,"@y"),relative_time(_time,"+1y"),_time)
 | where _time <= info_max_time

 | eval myMonth = strftime(_time, "%Y-%m") 

 | rename COMMENT as "Chart the results"
 | chart sum(myValue)  over myType by myMonth