Have you tried ElseIF?
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
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
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
IF DATEDIFF('month',[Month of Placements],TODAY())>= 2 and [Batch Age]<=1 then
You can also break each [Amt Pmts] into its own calculation, and add the results.
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.
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.
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).
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)?
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?