topic Re: Amount between 2 dates in QlikView

Amount between 2 dates

thomas_skariah — Mon, 10 Jul 2017 07:31:26 GMT

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

Re: Amount between 2 dates

devarasu07 — Thu, 13 Jul 2017 04:51:31 GMT

Hi,

Can you try like below,

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

Thanks,

Deva

Re: Amount between 2 dates

thomas_skariah — Thu, 13 Jul 2017 05:24:23 GMT

Hi Deva,

Its shows 0.

Regards,

Tom

Re: Amount between 2 dates

olivetwist — Thu, 13 Jul 2017 15:17:39 GMT

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

Re: Amount between 2 dates

thomas_skariah — Thu, 13 Jul 2017 15:26:37 GMT

Hi Oliver,

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

Regards,

Tom

Re: Amount between 2 dates

Frank_Hartmann — Fri, 14 Jul 2017 08:25:42 GMT

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

Re: Amount between 2 dates

thomas_skariah — Fri, 14 Jul 2017 08:33:02 GMT

Hi Frank,

Thks for reply.

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

Regards,

Tom

Re: Amount between 2 dates

antoniotiman — Fri, 14 Jul 2017 09:57:11 GMT

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

Re: Amount between 2 dates

thomas_skariah — Fri, 14 Jul 2017 10:16:04 GMT

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

Re: Amount between 2 dates

antoniotiman — Fri, 14 Jul 2017 10:24:38 GMT

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.