New post
Question
· Aug 30, 2022

Newbie in need of assistance with Calculated Measures/Growth Rate

#InterSystems IRIS BI (DeepSee)

Hello,

I am still trying to figure that Calculated Measure stuff and I hope to learn from you. I have several cubes I am trying to create calculated measures using the Calculated Member Expression.

I have a cube called Cards. There is a measure called count that give total cards count of 15,000. There are two dimensions I am considering. One is the CreditGrade (A, B, C, D, nograde) and the other is the date of the scoredate.(2000-2015).

The CreditGrade dimension has two hierarchies. H1 being the creditnumber and H2 being the CreditGrade (The one I need).

The scoredate dimension has the scoreYear, ScoreQuarter, ScoreMonth, ScoreWeek.

Without adding any filters, there are 15,000 cards. When I add filters such as ClosedCards, ChargedoffCards, the total number of cards drops to 12,500.

1)I try to calculate the percentage of each individual grade based on the 12,500 and not the 15,000 (When I used [Measures].[%Count]/%MDX("Select From [Cards]"), I get the percentage based on the 15,000. But I would like the percentage based on the 12,500 which is what I am interested in.

2) On the columns, I have the ScoreDate (2000....2015) and the rows I have the ScoreGrade of A, B, C, D. as follows:

Scoreyear          A      B      C      D   

2000                  200   150  85    15

2001                   275    300  25   25

and so forth all the way to 2015

I want to create a measure (growth rate) for each ScoreGrade throughout the years. For example, growth rate of A from 2000 to 2001, 2001 to 2002, 2002 to 2003 all the way to 2014 to 2015. From the table above, it should be (150-200)/200.; (85-150)/150; (15-85)/85 and so on. I would like this formula to be applied to the entire 12,500 and not the 15,000 so that each grade will show its growth rates over the years.

I am not sure how to go about it. I read the documentation, tried to apply it but it did not work. I got NA.

The MDX Query is as follows:

SELECT NON EMPTY [scoreGrade].[H2].[Scoregrade].Customers ON 0, NON EMPTY [ScoreDate].[H1].[ScoreYear].Customers ON 1 FROM [CARDS] %FILTER[charge-off].%NOY,[Closed].%NOT

The MDX Query for the ScoreYear is: SELECT NON EMPTY [ScoreDate].[H1].[ScoreYear].Customers ON 1 FROM [CARDS]

The MDX Query for the ScoreGrade is: SELECT NON EMPTY [ScoreGrade].[H2].[ScoreGrade].Customers ON 0 FROM [CARDS].

Thank you for your help. This will a start for me. 

Discussion (2)1
Log in or sign up to continue
Max Maduri · Aug 31, 2022

Hi Alexander,

I am a newbie so trying to even understand the documentation. I created a pivot table from the Cards cube. The %CONTEXT applies only to a KPI, is it correct?

When it comes to part 2), I also reviewed the %CELL function, I tried to use it but since I am new to Deepsee, I keep messing up:).

Let's say I have the following pivot table

ScoreGrade 2000 2001 2002 2003 2004
A 200 150 85 15 30
B 275 300 25 25 45
C 35 60 55 50 57
D 400 450 350 250 200

This table is based off the following MDX query:

SELECT NON EMPTY [ScoreDate].[H1].[Score Year].Customers ON 0, NON EMPTY [ScoreGrade].[H2].[Scorer].Customers ON 1 FROM [CARDS]

In order to create an expression that will calculate the growth rate from 2000 to 2001, 2001 t0 2002 and so forth, We are using %CELL. I am simply confused as when I use it, I don't get the correct information.

0 0