13 Replies Latest reply on Jun 4, 2018 10:17 AM by rebecca.johnson.4

# how do you (use a function) to identify spikes in a time series plot?

I am doing a time series analysis (line plot) and I'd like to be able to use an algorithm to identify the "spikes". right now I'm just visually identifying the spikes but I'd prefer to automate that. How do I do that?

• ###### 1. Re: how do you (use a function) to identify spikes in a time series plot?

Hi Ben

If my understanding is right use can use of KPI to identify the spikes if its above some value . Let me know if this is what you are looking for.

Thanks

Karthik

• ###### 2. Re: how do you (use a function) to identify spikes in a time series plot?
 series field 2 number time a adsfs 4 2000 a jkjhkhjk 3 2001 a dfsjk 9 2002 a adsfdsa 7 2003 b sdfdfff 3 2000 b ddddd 3 2001 b ssss 3 2002 b sdsdre3 3 2003

take this fake data for example. i have 2 series, A and B. when each is plotted over time, A spikes up. How do I use KPI to identify this?

in my real data, i have 1000s of series. I can think of 1 way to do this and that would be to create a new field in my dataset that manually calculates the number as a percent of the series sum. is it better to do it that way or can I implement this in tableau?

• ###### 3. Re: how do you (use a function) to identify spikes in a time series plot?

You should be able to do with table calculations, sort of a quick SPC. I'm happy to help with this, but need a bit more data to work with. If you could mock something up in excel and attach the file (use the advanced editor for that) I'll have a look. Please be sure your time field matches the one in your data source as much as possible, i.e. in terms of type, nulls, etc. Are those years? Also, field 2 doesn't serve any purpose in this sample, correct?

N.

• ###### 4. Re: how do you (use a function) to identify spikes in a time series plot?

actually, field 2 does serve a purpose. think of it like a semi-unique identifier in the data set where most of the time it's unique but on occasion there are rows with the same field 2 name.

time is in years.

• ###### 5. Re: how do you (use a function) to identify spikes in a time series plot?

But it doesn't come into play in terms of spikes, right? How many years are in your data? Are all series present for all years (it isn't a deal breaker if that is not the case, but I'd have to use my brain slightly more)? I should be able to do something with superstore sales. Do you have a threshold in mind for a spike? If not I'll probably just use 2 or 3 standard deviations?

• ###### 6. Re: how do you (use a function) to identify spikes in a time series plot?

i guess it doesn't come into play with regards to spikes.

about 13 yrs total. series are not all present in all years. some series are just 1 year and some span multiple.

no clue on the threshold. as of now I was identifying it visually, i'd prefer to use a more statistical approach. 1-3 SD seems reasonable.

• ###### 7. Re: how do you (use a function) to identify spikes in a time series plot?

Missing values count as zeros?

• ###### 8. Re: how do you (use a function) to identify spikes in a time series plot?

I didn't do much to make it pretty, but this should basically do the job (if I understand it). I used month-year to get enough dates to be interesting and put in a parameter for the alert threshold. Note, I only setup an upper control limit, but lower would be similar if you want that too. Also, on the last sheet I filter using an additional window calc, so you just see

• ###### 9. Re: how do you (use a function) to identify spikes in a time series plot?

Pretty now.

See if this would work for your needs, let me know if you'd like me to fill in some of the details.

N.

1 of 1 people found this helpful
• ###### 10. Re: how do you (use a function) to identify spikes in a time series plot?

i'm learning a lot from your set. I still don't understand all the logic. let's start with:

2) why you have averagessales and Averagesales + N*SD in the Marks.

Also, the plot itself is not exactly what i was trying to do. how would you create a flag that identifies States that have peaks? for example, arizona has 2 peaks and Baghdad has none. I'd like to quantify the volatility somehow.

• ###### 11. Re: how do you (use a function) to identify spikes in a time series plot?

here's another one. in the quickSPC example, lets say that each state did not necessarily have continuous sales in each month or year. maybe California only had sales in 1 year and england had sales from 2010-2012 and Bahia had sales in 2000 and in 2002. how do you count the number of years (or whatever increment)?

that seems complicated. or even something more simple, identify the states that only had sales in 1 year.

• ###### 12. Re: how do you (use a function) to identify spikes in a time series plot?

2) If I recall correctly, average sales was just an intermediate calculation on my way to Average Sales+N*SD

1) Has alert is precisely those "States" where at least one alert occurs. Alert is 1 when there is an alert and 0 when there isn't, Has alert is simply the max of alert over all the dates in the view. In a situation like yours, with 1000s of series, it is probably a good idea to narrow down just to the ones which might be interesting (important). I'll sometimes go a bit further and restrict only to alerts in the recent couple of weeks (months or years). You can do that using the optional parameters in the window_max, so you're just considering the last few records.

SD is a measure of volatility. If you want a count of the alerts, you could use window_sum instead of window_max (indicator variables are handy in this way).

I asked about how null records should be handled, but after waiting for 30 seconds, I made a judgement call. It seemed to me the most reasonable way to deal with missing records is to call then 0 (one could also count them as missing... but that gets messier philosophically). To count the missing records as zero, I created a calculation called Sum Sales which is: zn(Sum([Sales])), this was used in place of the usual sum([Sales]). Layout of the worksheet plays a role too, it is why I switched from a columnar format on the first sheet to a tabular format those that followed, it wasn't just an aesthetic choice (though it was that too). Laying things out in a tabular way like this allows you to "densify" your data so that all row/column combinations are represented in a way that Tableau recognizes. Zn works if there is a null record, but not if there is no record, this layout creates the structure on which to hang this calculation. If you find that confusing... you're not alone.

• ###### 13. Re: how do you (use a function) to identify spikes in a time series plot?

I used the Alert = 1 in the filters section instead of using Has Alert = 1, because i only wanted to see the Items that were of interest.