Dimension specific LINEST_M values in a table

craig_powell — Wed, 09 Dec 2015 11:44:47 GMT

Hi,

I have a requirement to list in a table the Dimension value and its appropriate 'M' value calculated using LINEST_M function.

However I am getting the same M value across all Dimensions rather than diffrent values.

Example of Data:

BranchSales:

LOAD * INLINE

[

BranchID, Month, Sales

'Branch A', 1, 2000

'Branch A', 2, 3000

'Branch A', 3, 4000

'Branch A', 4, 5000

'Branch A', 5, 6000

'Branch A', 6, 7000

'Branch B', 1, 8000

'Branch B', 2, 9000

'Branch B', 3, 8500

'Branch B', 4, 7500

'Branch B', 5, 6500

'Branch B', 6, 5500

'Branch B', 7, 4500

'Branch B', 8, 3500

];

Output:

Re: Dimension specific LINEST_M values in a table

craig_powell — Wed, 09 Dec 2015 22:17:12 GMT

I worked out the answer

LINEST_M(Total <Branch> aggr(if(Sum(Sales),Sum(Sales)),Month,Branch),Month)