question

greglipman-4888 avatar image
0 Votes"
greglipman-4888 asked greglipman-4888 action

dynamic columns with accumulated sum - tricky tricky

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-servicessql-server-analysis-services
image.png (81.1 KiB)
image.png (42.5 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AlexeiStoyanovsky avatar image
0 Votes"
AlexeiStoyanovsky answered AlexeiStoyanovsky commented

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.

· 14
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Alexei

not sure I understood - what is the full query and will it work:
I added but does not compile

with member [Measures].tri_cell as
SUM
(
{NULL : [Dor Time].[Calendar Quarter].CurrentMember}
,[Measures].[Inc Amt]
)
select NON EMPTY
(
Lead
(
LinkMember
(
[Dol Time].[Calendar Quarter].CurrentMember
,[Dor Time].[Calendar Quarter]
)
,[quarter offset].[quarter offset].CurrentMember.Key0
)
,[measures].[tri_cell]
)

0 Votes 0 ·

The MDX I posted is for a calculated measure that you'll need to declare first. Next you plot your 'quarter offset' attribute on columns, DoL quarters on rows, and this calculated measure in cells.

0 Votes 0 ·

Alexei

lets me sleep on it or rather learn more
i am lost but will go thru all the new terms
not sure where quarter offset came from too
thanks for the idea will be back!

0 Votes 0 ·
Show more comments
greglipman-4888 avatar image
0 Votes"
greglipman-4888 answered

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]

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.