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

    Combine multiple IF calcs into 1 calc?

    Ben Perlman

      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?
          Soham Wadekar

          Have you tried ElseIF?

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

            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

              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

                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

                  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

                    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

                      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

                        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

                          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?