As you mentioned, X and Y values need to be numeric, so read your dates in as numbers:
Then, you need to decide what you want to do with multiple price values per date and material as for material B in your sample. I've decided to average these values.
If you want to match the linest_b value the value shown as trend line in the line chart, you need to correct the x-value by subtracting the min value.
Then your script could look like
Set DateFormat = 'MM/DD/YYYY'; INPUT: Load * Inline [ Material, InvoiceDate, Price, A,01/01/2015,60 A,01/02/2015,30 A,01/03/2015,40 B,01/01/2015,12 B,01/01/2015,18 B,01/01/2015,10 B,01/01/2015,16 ]; LEFT JOIN LOAD Material, Min(InvoiceDate) as MinDate Resident INPUT GROUP BY Material; LOAD LINEST_M( AvgPrice, InvoiceDate) as M, LINEST_B(AvgPrice, InvoiceDate-MinDate) as B, Material GROUP BY Material; LOAD Material, InvoiceDate, Only(MinDate) as MinDate, Avg(Price) as AvgPrice Resident INPUT GROUP BY Material, InvoiceDate;
Which will only give a M / B values for material A, since you only have one X value for material B.
Compare the values for trend line in chart, see attached sample.
LinestScript.qvw 157.5 K
You would only calculate this in the script when the result shouldn't change when you make selections.
In general, calculation of linest_m and linest_b should not differ between QS and QV, here is an example in QS:
Note that the example chosen by Michael is not the best possible, the dates on the axis are not sequential, so the linear trend doesn't look linear.
Hi Swuehl, I have tried create the same thing but my data has gaps, as pasted below. The values a get are different to the ones in the graph; is it possible to achieve the same slope and intercept when you have gaps?