
1. Re: Combine multiple IF calcs into 1 calc?
Soham Wadekar Apr 19, 2018 10:37 AM (in response to Ben Perlman)Have you tried ElseIF?

2. Re: Combine multiple IF calcs into 1 calc?
Ben Perlman Apr 19, 2018 10:39 AM (in response to Soham Wadekar)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?
Tim Dines Apr 19, 2018 10:48 AM (in response to Ben Perlman)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?
Branden Kornell Apr 19, 2018 11:01 AM (in response to Ben Perlman)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?
Ben Perlman Apr 19, 2018 11:17 AM (in response to Branden Kornell)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?
Branden Kornell Apr 19, 2018 11:53 AM (in response to Ben Perlman)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?
Ben Perlman Apr 19, 2018 11:58 AM (in response to Branden Kornell)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?
Branden Kornell Apr 19, 2018 2:55 PM (in response to Ben Perlman)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.
What's your LOD look like?
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?
Ben Perlman Apr 19, 2018 6:38 PM (in response to Branden Kornell)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?