3 Replies Latest reply: Sep 28, 2011 5:10 PM by Stefan Wühl RSS

    Date +1

      Good morning,

       

      I have a formula that keeps coming back $0, but I know there are totals.  I am trying to build a chart that shows how many days a payment is made after it is due.  1 day, 2 days, etc etc.  So, i have a due date, paid date, and customer numbers.  The formula that i have is:

       

       

      sum(if([Due.Due Date]+2=Paid.TransactionDate and [Due.CustomerNbr]=Paid.CustomerNbr, Paid.TransactionAmount,'try again'))

       

      Any and all help is appreciated as always!

       

      Thank you,

      Kristy

        • Date +1
          Stefan Wühl

          Good morning Kristy,

           

          could you tell us a little more about your data model, especially how the tables Due and Paid are linked?

           

          How / where do you use above expression? In a chart? If so, what are your dimensions?

           

          Have you tried with either one of your two AND operands only?

           

          Regards,

          Stefan

           

          P.S: I think it will not affect your results, but personally I wouldn't use a string as part of my sum ('try again').

            • Date +1

              The due files are linked by the customer number, loan number.  I have a concatenated field in the script that states that the due customer number+due loan number+(due date+1) equals the due.match1.  If due.match1=paid date then I want the paid transaction amount.  That field would be called - paid 1 day late because if something is due 9/12 but paid on 9/13 the due+1 field should give me 9/13 - right? 

               

              This is an expression in a pivot table.  the dimensions are the current month - =if(month(Today())=month((Due.Paid.Date)),Due.Paid.Date,)

               

              Yes, I have tried the formula = sum(if(([Due.Due Date]+1)= Paid.TranDate and Paid.CustomerNbr=Due.CustomerNbr, Paid.TranAmount,))

               

              I realize that about then 'Then' statement of the expression.  I wanted to see if it actually resulted in the string.  Since I know there is a total but it is coming up $0 instead of Try Again, I think there is something incorrect in my expression.

               

              Thanks,

              Kristy

               

               

                • Date +1
                  Stefan Wühl

                   

                  The due files are linked by the customer number, loan number.  I have a concatenated field in the script that states that the due customer number+due loan number+(due date+1) equals the due.match1.  If due.match1=paid date then I want the paid transaction amount.  That field would be called - paid 1 day late because if something is due 9/12 but paid on 9/13 the due+1 field should give me 9/13 - right? 

                  Hm I don't really understand, how does your script line for this looks like?

                  If you concatenate a customer number and loan number and (due date+1) you are getting a date to compare with paid date?

                   

                  I assume your dates are correctly recognizes as Date type, so your calculated dimension returns correct dates for this month?

                   

                  My point in not using a string in the else part of your if() function is, that you want get your string back if all or any records will lead to executing your else-statement, because of the sum (I believe you can't sum a string, It will return zero at best). If you need to verify that the else part is executed, you might change the then part to zero and use 1 for the else part as in

                  = sum(if(([Due.Due Date]+1)= Paid.TranDate and Paid.CustomerNbr=Due.CustomerNbr, 0,1))

                   

                  if you use this as first expression and the counter part

                  = sum(if(([Due.Due Date]+1)= Paid.TranDate and Paid.CustomerNbr=Due.CustomerNbr, 1,0))

                   

                  then the sum of both values should be equal to the number of records for that date.

                   

                  I don't see anything really wrong with your expression but maybe I am missing something.

                   

                  Try using separate expressions for part of your total expression, like

                  = [Due.Due Date]

                  = [Due.Due Date]+1

                  = Paid.TranDate

                   

                  etc.

                   

                  Maybe there is an issue with your date comparison, try also putting date around both parts:

                  = sum(if(date([Due.Due Date]+1)= date(Paid.TranDate) and Paid.CustomerNbr=Due.CustomerNbr, Paid.TranAmount))

                   

                  If you have still problems, maybe you could post a small app here in the forum which shows your problem (it doesnt need to use your original data if this is a problem). At best, it is self contained, i.e. it is possible to reload (no need for further files, e.g. by using INLINE LOADs). Upload is available from the advanced editor.

                   

                  Hope this helps a little,

                  Stefan