Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
thomas_skariah
Creator III
Creator III

Amount between 2 dates

Hi All,

Can anyone help from below scenario

I have a report with columns like ID, Date and Amount (other columns as well check attach file)

I created an additional date column adding of +14 days to the date, i.e if the date is 1-Jul-2017, the new date column will give date as 15-Jul-2017. Now i have to create a new column which will add up all the amount which will come within this 14 days. i.e if 1-Jul-2017 got amount 100, then the next (within the same ID) 7-Jul-2017 with amount 200 and next 11-Jul-2017 with amount 300. the total should give 600 in 1-Jul-2017 date row.

Note: it should not add up the other ID data.

Check the example in attached file.

Regards,

Tom

9 Replies
devarasu07
Master II
Master II

Hi,

Can you try like below,


=SUM({<Date = {">=$(=Date(WeekStart(Min(Date))))<=$(=Date(WeekStart(Min(Date))+14))"}>}Amount)


Thanks,

Deva

thomas_skariah
Creator III
Creator III
Author

Hi Deva,

Its shows 0.

Regards,

Tom

olivetwist
Creator
Creator

Are you trying to do this in the load script or in the object?

thomas_skariah
Creator III
Creator III
Author

Hi Oliver,

I'm trying to do it in front end, but back end also preferable.

Regards,

Tom

Frank_Hartmann
Master II
Master II

LOAD ID,

     [Doc No.],

     Method,

     Process,

     Date(Date) as Date,

     Date(Date(Date)+14) as Date_14,

     if(Date(Date)<=Date(Date) and Date(Date) <=Date(Date(Date)+14),(Amount)) as NewAmount,

     [Date + 14Days],

     Amount

FROM

(biff, embedded labels, table is Sheet2$);

see attached file

hope this helps

thomas_skariah
Creator III
Creator III
Author

Hi Frank,

Thks for reply.

I cannot see the required output. It showing the original Amount values in Backend named column.

Regards,

Tom

antoniotiman
Master III
Master III

Hi Thomas,

this is Logic in Script.

However there is a limit : 2 next days are evaluated.

If You need to increase this value You change script according max number days .

Temp:
LOAD ID,
[Doc No.],
Method,
Process,
Date(Date) as Date,
// [Date + 14Days],
   Amount
// [Amount + within 14 Days Amount],
// Remarks
FROM
"QC-1.xls"
(biff, embedded labels, table is Sheet2$);
LOAD *,
If(ID <> Peek(ID),Amount,
If(ID = Peek(ID,-2) and Date+14 >= Peek(Date) and Date+14 >= Peek(Date,-2),RangeSum(Peek(Amount),Peek(Amount,-2),Amount),
If(Date+14 >= Peek(Date),RangeSum(Peek(Amount),Amount),Amount))) as Amount1
Resident Temp
Order By ID,Date Desc;
Drop Table Temp; 

Regards,

Antonio

thomas_skariah
Creator III
Creator III
Author

Hi Antonio,

Thks for your reply.

If i have to evaluate next 3 days then where in script I have to do the changes, I checked by changing the date and amount to -3 from -2 but it shows wrong value.


Regards,

Tom

antoniotiman
Master III
Master III

Try this

LOAD *,
If(ID <> Peek(ID),Amount,
If(ID = Peek(ID,-2) and ID = Peek(ID,-3) and Date+14 >= Peek(Date) and Date+14 >= Peek(Date,-2) and Date+14 >= Peek(Date,-3),RangeSum(Peek(Amount),Peek(Amount,-2),Peek(Amount,-3),Amount),
If(ID = Peek(ID,-2) and Date+14 >= Peek(Date) and Date+14 >= Peek(Date,-2),RangeSum(Peek(Amount),Peek(Amount,-2),Amount),
If(Date+14 >= Peek(Date),RangeSum(Peek(Amount),Amount),Amount)))) as
Amount1

I can't test this because I haven't data.