Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Sum if Date1=Date2+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.

3 Replies
Not applicable

Sum if Date1=Date2+1

try this:

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

MVP
MVP

Sum if Date1=Date2+1

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.

Not applicable

Sum if Date1=Date2+1

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.