dynamic columns with accumulated sum - tricky tricky

greg lipman 61 Reputation points
2021-04-03T13:18:40.303+00:00

at the first image is what I have now:
on rows i have calendar quarters where each row contains claims (like auto insurance claims)
that happened that quarter (Date of Loss)
on columns I need say 12 quaters of Payment Date (DoR dimension)
that show accumulated sum of payments on those claims
the problem is that those column quarters are sort of hard coded - for each
row they should start at that row's quarter as in image 2
it makes sense - for row 2018 Q1 payments will start in that
quarter and later not in the PAST!!!!
so for each row quarter i need in columns to show accumulated
sum in quarters srarting with that row quarter but in DoR dimension
that way the triangle will be on different diagonal because
for say last year rows will not be enough (12) FUTURE columns

on second image it is correct i created it in c# code of course

84177-image.png

with member [Measures].tri_cell as
SUM
(
{NULL : [Dor Time].[Calendar Quarter].CurrentMember}
,[Measures].[Inc Amt]
)
select NON EMPTY
[Dor Time].[Calendar Quarter].[Calendar Quarter].members * {[Measures].tri_cell}
on columns,
[Dol Time].[Calendar Quarter].[Calendar Quarter].members on rows
from pandl

83898-image.png

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,793 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,243 questions
0 comments No comments
{count} votes

Accepted answer
  1. Alexei Stoyanovsky 3,411 Reputation points
    2021-04-05T08:13:25.353+00:00

    So basically to arrive at a cell's value, you start with the DoL quarter for the row, take the same quarter from DoR, move it forward by the number of quarters corresponding to the column, and finally take the value of some measure for this resulting quarter. You'll need an unrelated 'quarter offset' attribute to plot on columns, but then the MDX follows your logic quite literally.

    (
      Lead
      (
        LinkMember
        (
          [Dol Time].[Calendar Quarter].CurrentMember
         ,[Dor Time].[Calendar Quarter]
        )
       ,[quarter offset].[quarter offset].CurrentMember.Key0
      )
     ,[measures].[tri_cell]
    )
    

    This implies that your codes for DoL and DoR quarters are the same. Also, tri_cell as it is now will repeat the last value for all the following quarters.


1 additional answer

Sort by: Most helpful
  1. greg lipman 61 Reputation points
    2021-04-06T12:42:26.667+00:00

    final version
    Alexei - !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
    will not do attach it is just like the second image originally uploaded done by c#

    with member [Measures].tri_cell_2 as
    Sum(
    { NULL :
    LinkMember
    (
    [Dol Time].[Calendar Quarter].CurrentMember
    ,[Dor Time].[Calendar Quarter]
    ).Lead(
    [Dim State].[State Key].CurrentMember.Properties("Key0")
    )}
    ,[Measures].[Inc Amt]
    )
    select [Dim State].[State Key].[State Key].members
    on columns,
    [Dol Time].[Calendar Quarter].[Calendar Quarter].members on rows
    from pandl
    where [Measures].[tri_cell_2]

    0 comments No comments