topic Sum if Date1=Date2+1 in QlikView

Sum if Date1=Date2+1

Fri, 02 Dec 2011 20:27:45 GMT

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.

Sum if Date1=Date2+1

Fri, 02 Dec 2011 20:49:57 GMT

try this:

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

Sum if Date1=Date2+1

johnw — Fri, 02 Dec 2011 20:50:27 GMT

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.

Sum if Date1=Date2+1

Fri, 02 Dec 2011 21:08:03 GMT

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.