9 Replies Latest reply on Apr 19, 2018 6:38 PM by Ben Perlman

# Combine multiple IF calcs into 1 calc?

I have 12 different calcs which show the amounts that were paid in a certain time frame based on when the account was placed with us (collection agency).

The end result is calculating liquidation percentages at each month level.  (liquidation = sum(amount paid)/sum(amount placed))

Right now there are 36 calcs that have to be done, and I can only get them to print in table format, can't do a line graph because they are all separate pills.

To start for payments, is there a way to combine these all into 1 formula?:

I have a separate if calc for each month level (1-12) to determine the number of payments received based on the age of the batch (how long it took the account to be paid after being placed with us).

1 month payments:

IF DATEDIFF('month',[Month of Placements],TODAY())>= 1 and [Batch Age]<=0 then

[Amt Pmts]

ELSE

0

END

2 month payments:

IF DATEDIFF('month',[Month of Placements],TODAY())>= 2 and [Batch Age]<=1 then

[Amt Pmts]

ELSE

0

END

3 month payments:

IF DATEDIFF('month',[Month of Placements],TODAY())>= 3 and [Batch Age]<=2 then

[Amt Pmts]

ELSE

0

END

this continues for 9 other formulas, always increasing the datediff by 1, and increasing the batch age by 1.

• ###### 1. Re: Combine multiple IF calcs into 1 calc?

Have you tried ElseIF?

• ###### 2. Re: Combine multiple IF calcs into 1 calc?

I have, but because the data overlaps it doesn't work.

The formula totals up all the payments from a certain period of months and before (like a running sum), so a payment can be in the 1 month payments and also the 2 month payments bucket

• ###### 3. Re: Combine multiple IF calcs into 1 calc?

You could do it as a case statement.  It would start something like this:

CASE DATEDIFF('month',[Month of Placements],TODAY())

WHEN 1 THEN IF [Batch Age]<=0 THEN 'Hello' END

WHEN 2 THEN IF [Batch Age]<=1 THEN 'Goodbye' END

END

• ###### 4. Re: Combine multiple IF calcs into 1 calc?

It'll be lengthy, but yes, you can combine IF statements in one formula. For instance, this is valid for adding the first two payment calculations.

IF DATEDIFF('month',[Month of Placements],TODAY())>= 1 and [Batch Age]<=0 then

[Amt Pmts]

ELSE

0

END

+

IF DATEDIFF('month',[Month of Placements],TODAY())>= 2 and [Batch Age]<=1 then

[Amt Pmts]

ELSE

0

END

You can also break each [Amt Pmts] into its own calculation, and add the results.

• ###### 5. Re: Combine multiple IF calcs into 1 calc?

The issue I think is that I need to create a dimension for "Payment month" based on what bucket it falls in.  If it falls into the "2" month bucket it will be in 1 and 2.  If it is "3" it will be in 1,2, and 3, etc.

Maybe I could create a calc to first label the dimension as either 1,2,3...12 first?

The problem with doing this with IFs is that once it fills out a value for the 1 month, it won't fill out a value for 2 month because there is already a value there for 1 month.

• ###### 6. Re: Combine multiple IF calcs into 1 calc?

No; if a single row can fall into multiple buckets, then it can't be a dimension. (Unless you change the entire data structure to split it into multiple rows.)

Since there are only 12 months, I think your best bet is to create 12 calculated fields: [Month1], [Month2], etc.

Then you can combine those as appropriate.

• ###### 7. Re: Combine multiple IF calcs into 1 calc?

That's what I have now, but I have 12 calcs for amount of payment, then another 12 calcs for amount of placements, and then another 12 calcs for sum(amount of payment)/sum(amount of placement) which I'm calling liquidity.

So that's 36 calcs, and if I want to show a running sum of liquidity % in a line graph, I am unable to do that.

I made 3 fixed LOD calcs to try to solve this, but the data is just a bit off (its good for 3 or 4 months and then it is inaccurate).

• ###### 8. Re: Combine multiple IF calcs into 1 calc?

Okay, I see your issue. You're right that you're trying to graph it on a dimension of 1 month, 2 month, etc. that's not really in the data.

Can you get accurate results if you just create line charts for sum(amount paid) and sum(amount placed)?

• ###### 9. Re: Combine multiple IF calcs into 1 calc?

I can't do line charts on sum amount paid and sum amount placed because it depends on the age of the batch (when it was placed compared to paid- the datediff between the two), and also the current date.

I'll post the 3 calcs it takes to get one month of liquidity, and then the LOD calcs below:

for 3 month liquidity:

3 month payments:

3 month placements:

3 month liquidity:

My LOD calcs, I have just 3, and they don't incorporate the first part of the above formulas (the datediff of either the payment and the placement, and today)

Liquidity running sum calc:

RUNNING_SUM(sum([Payments LOD])/sum([Placements LOD Calc]))

I'm thinking of making one big formula to calculate a dimension for "1 month payment" and "1 month placement" based on a number of more complex if statements than I currently have? and then another liquidity calc based off that?