Dynamically multiplying columns together for all combinations

JordanJ

Hi all,

I've been stuck on this for a while now and can't find anything on the discussion board on issue. 

I have 5 tables, and each have a number (in the multiple column) for a given year or scenario. I've successfully created a list of all possible combinations that can be created from these tables, but my question is how do I then create a list of all combinations with the percentages in the 'multiples' columns multiplied together?

For example, let's say the calendar year is 2016, the class is Preferred Plus NT, the face amount is $0-$249,999, the issue year is 1-2, and the next issue year is also 1-2, so I'd have 1.1% * 2.1% * 4.1% * 5.1% * 6.1% for that scenario. I then want to calculate the same thing for all possible combinations that can be created from the five tables.

I was going to brute force this with a formula tool, but I was hoping there is a more elegant way, and one that would account for the percentages being updated in the spreadsheet. Any ideas on how to do this?

Thank you!

All combinations.yxmd

danilang

Hi @JordanJ 

Like @Inactive User said.  You have 5 tables here so treat them as such.  

For each table, use a Select tool to take only the columns you need.  Filter out the null rows.  If the table has an Issue Years field then you need to split the set in 2: one set <= Cut off year and after the cut-off year and append these values with the calendar year table which has also been split based on the cutoff year.  The append tool will automatically generate all the possible combinations for you.  Union the pre-cutoff and post-cutoff record  This handles the Class and Policy year tables.  The Policy Year 2 and Face Amount Band don't have year ranges associated with them so just append the values.  Since you now have all the combinations, the final formula tool just applies one formula to all the rows resulting in

Dan

All combinations V2.yxzp