Sum Date Range

[Anonymous]

Hi All,

I have the following sum date range that I need some help.  Everything works fine until I added a new field "Type".

=Sum({<My_Date = {"$(='>='& Date(vDateStart) & '<=' & Date(vDateEnd))"}>}+1000) + Sum(Amount)

For Example:  I have2 variables, Start and End date where I want to increase by 1000.

• I have selected 5/1/16 & 5/1/16 ; both of these dates have zero amount
• I want to increase by 1000
• If you look at the Sum Range column highlighted in green, Amount were off. I expect date range 5/1 and 5/2 to be 1000

Fund	Company	My_Date	Type	Original Amount	Sum Range
ABCD	EFG	7/1/2015	Cost	-1,000,000	-1,000,000
ABCD	EFG	7/2/2015	Cost	-2,000,000	-2,000,000
ABCD	EFG	8/1/2015	Proceeds	750,000	750,000
ABCD	EFG	8/7/2015	Proceeds	5,000	5,000
ABCD	EFG	8/14/2015	Proceeds	1,250,000	1,250,000
ABCD	EFG	9/4/2015	Proceeds	1,000	1,000
ABCD	EFG	9/18/2015	Proceeds	1,000,000	1,000,000
ABCD	EFG	05/01/2016	Cost	0	1,000
ABCD	EFG	05/01/2016	EMV	0	1,000
ABCD	EFG	05/01/2016	Proceeds	0	6,000
ABCD	EFG	05/02/2016	Cost	0	2,000
ABCD	EFG	05/02/2016	EMV	0	2,000
ABCD	EFG	05/02/2016	Proceeds	0	7,000

Any inputs will greatly appreciated!

Thanks,

Frank

[sunny_talwar]

Would you be able to share a sample to see what is going on

[sunny_talwar]

Just out of curiosity, can you try this?

=Avg({<My_Date = {"$(='>='& Date(vDateStart) & '<=' & Date(vDateEnd))"}>}1000) + Sum(Amount)

[sunny_talwar]

Or this:

=Only({<My_Date = {"$(='>='& Date(vDateStart) & '<=' & Date(vDateEnd))"}>}1000) + Sum(Amount)

[Anonymous]

Sunny,

Both didn't works. I use below and it works.  But the "Range Sum" field didn't total up where I tried both Expression and Sum row total.  Would know why?  Attached sample QVW.

=Aggr(RangeSum(Sum({<My_Date = {"$(='>='& Date(vDateStart) & '<=' & Date(vDateEnd))"}>} Amount) + Avg({<My_Date = {"$(='>='& Date(vDateStart) & '<=' & Date(vDateEnd))"}>}vIRR),
Sum({<My_Date -= {"$(='>='& Date(vDateStart) & '<=' & Date(vDateEnd))"}>} Amount)), My_Date,Company,Fund,TransType)

Many Thanks,

Frank

[sunny_talwar · Accepted solution]

See if one of the two is what you want

[Anonymous]

Thanks again Sunny!!..

I missed the sum infront of the Aggr.

[sunny_talwar]

Yup

[Anonymous]

Sunny,

I have another question, hope you can assist. Base on the Start and End date with amount of 1000.  how can I include this amount 1000 to the date range where I selected Trans Type = 'EMV" ?

For example:

If I have a date range of 5/1/16 - 5/5/16 and Amount of 1000 where I selected Trans Type='EMV' on the listbox.  I just want the amount 1000 to sum up on these date range where Trans Type='EMV'.  Below works if I set = 'EMV', what if I selected 'Cost' or 'Proceeds' for example:

=Sum(Aggr(RangeSum(Sum({<My_Date = {"$(='>='& Date(vDateStart) & '<=' & Date(vDateEnd))"}>} Amount) + Avg({<My_Date = {"$(='>='& Date(vDateStart) & '<=' & Date(vDateEnd))"}, TransType={'EMV'}>}vIRR),
Sum({<My_Date -= {"$(='>='& Date(vDateStart) & '<=' & Date(vDateEnd))"}>} Amount)), My_Date,Company,Fund,TransType))

Many Thanks,

Frank

Fund	Company	My_Date	TransType	Amount	Range Sum (1)
ABCD	EFG	7/1/2015	Cost	-1,000,000	-1,000,000
ABCD	EFG	7/2/2015	Cost	-2,000,000	-2,000,000
ABCD	EFG	8/1/2015	Proceeds	750,000	750,000
ABCD	EFG	8/7/2015	Proceeds	5,000	5,000
ABCD	EFG	8/14/2015	Proceeds	1,250,000	1,250,000
ABCD	EFG	9/4/2015	Proceeds	1,000	1,000
ABCD	EFG	9/18/2015	Proceeds	1,000,000	1,000,000
ABCD	EFG	05/01/2016	Cost	0	0
ABCD	EFG	05/01/2016	EMV	0	10
ABCD	EFG	05/01/2016	Proceeds	0	0
ABCD	EFG	05/02/2016	Cost	0	0
ABCD	EFG	05/02/2016	EMV	0	10
ABCD	EFG	05/02/2016	Proceeds	0	0
ABCD	EFG	05/03/2016	Cost	0	0
ABCD	EFG	05/03/2016	EMV	0	10
ABCD	EFG	05/03/2016	Proceeds	0	0
ABCD	EFG	05/04/2016	Cost	0	0
ABCD	EFG	05/04/2016	EMV	0	10
ABCD	EFG	05/04/2016	Proceeds	0	0
ABCD	EFG	05/05/2016	Cost	0	0
ABCD	EFG	05/05/2016	EMV	0	10
ABCD	EFG	05/05/2016	Proceeds	0	0
ABCD	EFG	Total		6,000	6,050

[sunny_talwar]

What happens if you don't add anything in there? I would think that if EMV is selected, by default it would put it only on those line where Trans Type is EMV. Is this automatic filtration not taking place?