# Thread: Using the LinRegSlope() function in Mondrian MDX

1. Junior Member
Join Date
Oct 2015
Posts
9

## Using the LinRegSlope() function in Mondrian MDX

I can't seem to get the correct result from the LinRegSlope function in Mondrian MDX. I have a cube with three dimensions:
• Station
• Year
• Month

and one of my measures is the Rain in mm.

For a fixed month (July) I want to calculate the slope of the regression line, with on the y-axis the average rainfall over the stations and on the x-axis the years:

As you can see, that slope should be -0.03.

To do the same in Saiku, I created the following MDX query:
WITH
SET [SelStations] as {[Station].[Station].Members}
SET [SelYears] as {[Year].[Year].Members}
MEMBER [Measures].[AvgMeasure] as Avg({[SelStations]},[Measures].[Rain (mm)])
MEMBER [Measures].[Slope] as LinRegSlope([SelYears],[Measures].[AvgMeasure])
SELECT
{[Measures].[Slope],[Measures].[AvgMeasure]} ON 0,
{[SelYears]} on 1
FROM
[StationData]
WHERE
{[Month].[Month].[7]}

But Saiku reports -3.72 as the regression slope.

I must be doing something wrong here, but I don't really understand what. Is there anyone who knows how to correctly use the LinRegSlope function?
Last edited by saschaz; 10-26-2015 at 06:25 AM.

2. Junior Member
Join Date
Oct 2015
Posts
9
What was missing is that the years have to be translated to an index, from 1 to the number of years. This can be done with the rank function:

WITH
SET [SelStations] as {[Station].[Station].Members}
SET [SelYears] as {[Year].[Year].Members}
SET [SelMonths] as {[Month].[Month].[7]}
MEMBER [Measures].[AvgMeasure] as Avg({[SelStations]},[Measures].[Rain (mm)])
MEMBER [Measures].[RankYear] as Rank([Year].[Year].CurrentMember,[Year].[Year].Members) // <- Create index
MEMBER [Measures].[Slope] as LinRegSlope([SelYears],[Measures].[AvgMeasure],[Measures].[RankYear])
SELECT
{[Measures].[Slope],[Measures].[AvgMeasure]} ON 0,
[Year].[Year].Members ON 1
FROM
[StationData]
WHERE
{[Month].[Month].[7]}