topic Date +1 in QlikView

Date +1

Tue, 27 Sep 2011 15:15:39 GMT

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

swuehl — Tue, 27 Sep 2011 16:38:12 GMT

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

Tue, 27 Sep 2011 18:48:40 GMT

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

swuehl — Wed, 28 Sep 2011 18:42:17 GMT

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