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

[Anonymous]

show's zero for the date range I selected.

I don't think it's filtering out. If I selected "Cost" for example with added amount of 1000, the other trans type for this date range fill in all with 1000, not just to "cost".

[sunny_talwar]

Hmmmm. let me test it out

[sunny_talwar]

The version I am checking in seems to filter out the results where Trans Type is not selected. Can you share a sample where this is not happening for you?

[sunny_talwar]

Can you check if RangeSum(2) is working the way you would expect?

[Anonymous]

it's works when I click on either of the TransType but if I clear it, the amount shows up on all TransType. Is it possible the rest of the TransType show's zero just "Cost" with1000 for the date range when I clear the listbox?

[sunny_talwar]

So are you saying when Nothing is selected Cost is the only one that should have 1000 and if the user select something else, then that one gets the value and rest all become 0?

[Anonymous]

No

Yes, If user selected something, only that type should gets  the value and the rest should be zero. I think your range 2 works but if I clear the selection everything fills in with 1000.  I guess, selection what trigger this 1000 in the range 2 sum. Maybe I think too much to retain that value even if I clear the selection.

I should be good with the range 2 sum.. Thanks again Sunny!!