Aggregating across dimension for a set of values

[#1]

Hi All,

in the attached QV app, I have two charts. the lower chart calculates the following:

total number of active accounts/ accounts at origination

syntax:
if(age>=0,(sum(tot_active_accts)/Sum({$<age ={$(=min(age))}>}total <vintage_YYYYMM> tot_active_accts)),0)

It does it for each vintage and vintage is one of the dimensions. In the chart above, what I want to calculate is the following:

total number of active accounts /accounts at origination for active vintages.

In other words, I want to apply a condition to the denominator so that denominator only sums tot_active_accts for vintages who have a maximum age of less than equal to the age on the dimension axis. For example, at age=72, I don't want the denominator to total active accounts of all vintages at age=0 but total active accounts at age 0 of all vintages that are still active at age=72.

Will much appreciate your help.

[sunny_talwar]

Are you looking to get something like this?

Expression:

If(age >= 0,(Sum(tot_active_accts)/(Sum(TOTAL tot_active_accts) - RangeSum(Above(TOTAL Sum(tot_active_accts), 1, RowNo(TOTAL))))), 0)

[#3]

not really. the chart should just give the weighted average of the chart below.

[sunny_talwar]

Would you be able to provide the numbers for the expected output may be in an Excel file by using the formulas so that one of us can help you better?

[#5]

How do I attach and Excel here?

[#6]

Ok, I pasted the Excel calculation below to demonstrate what I am trying to do. The current formula calculates the the denominator to be 474,147 for all ages, because it is calculating the sum of active accounts for all vintages at age=0. What I want the denominator to be is shows in red. the sum of only those vintages that are active at that age. So when age=130, only 200507 vintage should be included in the denominator, when age is 129, 200507 and 200508 vintage is included in the denominator, and so on. The denominator keeps increasing as age approaches zero. 

[sunny_talwar]

Check here for the instructions to upload a sample

Uploading a Sample

[#8]

created a new discussion with Excel

Aggregating across dimensions with multiple criteria