Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Using the LinRegSlope() function in Mondrian MDX

  1. #1
    Join Date
    Oct 2015
    Posts
    9

    Default 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?
    Attached Images Attached Images  
    Last edited by saschaz; 10-26-2015 at 06:25 AM.

  2. #2
    Join Date
    Oct 2015
    Posts
    9

    Default

    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]}

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.