Sum if Date1=Date2+1

[#1]

Good afternoon,

I have two spreadsheets.  One shows when we have called customers and the other one is the results of the call.  We have a call center and one sheet is where i get the call date and the account number.  the second spreadsheet shows when the date that person promised to pay and when they actually did pay. 

I need to build a chart that shows when we have called a person and they paid the same day, the next day, third day...on to 7 days from the date called.  This sounds straightforward but I built a pivot table that shows.

//total promised

sum(PromisetoPayamount)---WORKS

//same day

sum(if(CallDate=PTPDate, PTPAmount))--WORKS

//next day

sum(if(CallDate+1=PTPDate, PTPAmount)) -- Does not work

As always any assistance would be greatly appreciated.

[#2]

try this:

sum(if(Date(CallDate+1)=PTPDate, PTPAmount))

[johnw]

Assuming CallDate and PTPDate are both correctly-defined QlikView dates, your expressions all look correct to me.  So my guess is that they are NOT correctly-defined.  Try making a list box or straight table for num(Date).  You should see integer values - the number of days since December 30, 1899.  If that's not what you see, you don't have a QlikView date, and the issue is probably in your script where these fields are being defined.

[#4]

oh and i would like to tie the account number together on both of the sheets.  If there is a way to say

date1+1&"|"&CustNo=Date2&"|"&CustNo that would be the best.