Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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').
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
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