simple moving average with two variables

[himpor]

hi ,
i had the following data which is collected daily price of 50 itesms i.e. TIMESTAMP, CLOSE and SYMBOL
( eg. 31-jan-2018, PRD1, 320
01-Feb-2018, PRD1, 330
31-jan-2018, PRD2, 1500
01-Feb-2018, PRD1, 1520

).

i need to find all the product whose closing price is lets say greater than 10 day moving average, 30 day moving average etc. )

this query i am writing

input data
| fields SYMBOL, TIMESTAMP,CLOSE
| eval Date =strptime(TIMESTAMP, "%d-%b-%Y")
| fieldformat Date=strftime(Date, "%d-%b-%Y")
| sort Date
| trendline sma10(CLOSE) as DMA10
| trendline sma30(CLOSE) as DMA30
| table Date, SYMBOL, CLOSE, DMA10, DMA30
| sort -Date

however this query works well if i write for any one product but calculation fails when data is for both product and sma moving average is also incorrectly calculated

Please suggest the approach

[acharlieh]

As you're looking for a simple moving average with a window, instead of the trendline command, what about using the streamstats command ?

You can set the window / time window appropriately, and just have average(close) by symbol

[Sukisen1981 · Accepted solution]

SYMBOL is your product, right?
Instead of | table Date, SYMBOL, CLOSE, DMA10, DMA30 try stats values(Date),values(CLOSE),values(DMA10),values(DMA30) by SYMBOL