Amount between 2 dates

[thomas_skariah]

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

[devarasu07]

Hi,

Can you try like below,

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

Thanks,

Deva

[thomas_skariah]

Hi Deva,

Its shows 0.

Regards,

Tom

[olivetwist]

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

[thomas_skariah]

Hi Oliver,

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

Regards,

Tom

[Frank_Hartmann]

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]

Hi Frank,

Thks for reply.

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

Regards,

Tom

[antoniotiman]

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]

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]

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.