topic Re: Sum Date Range in QlikView

Sum Date Range

Anonymous — Tue, 19 Apr 2016 20:19:18 GMT

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

Re: Sum Date Range

sunny_talwar — Tue, 19 Apr 2016 20:53:47 GMT

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

Re: Sum Date Range

sunny_talwar — Tue, 19 Apr 2016 21:08:54 GMT

Just out of curiosity, can you try this?

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

Re: Sum Date Range

sunny_talwar — Tue, 19 Apr 2016 21:09:31 GMT

Or this:

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

Re: Sum Date Range

Anonymous — Tue, 19 Apr 2016 21:35:53 GMT

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

Re: Sum Date Range

sunny_talwar — Tue, 19 Apr 2016 21:39:42 GMT

See if one of the two is what you want

Re: Sum Date Range

Anonymous — Tue, 19 Apr 2016 21:50:14 GMT

Thanks again Sunny!!..

I missed the sum infront of the Aggr.

Re: Sum Date Range

sunny_talwar — Tue, 19 Apr 2016 21:54:07 GMT

Yup

Re: Sum Date Range

Anonymous — Thu, 21 Apr 2016 18:31:42 GMT

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

Re: Sum Date Range

sunny_talwar — Thu, 21 Apr 2016 18:38:09 GMT

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?

Re: Sum Date Range

Anonymous — Thu, 21 Apr 2016 19:04:33 GMT

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".

Re: Sum Date Range

sunny_talwar — Thu, 21 Apr 2016 19:10:22 GMT

Hmmmm. let me test it out

Re: Sum Date Range

sunny_talwar — Thu, 21 Apr 2016 19:14:26 GMT

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?

Re: Sum Date Range

sunny_talwar — Thu, 21 Apr 2016 19:19:47 GMT

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

Re: Sum Date Range

Anonymous — Thu, 21 Apr 2016 19:29:35 GMT

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?

Re: Sum Date Range

sunny_talwar — Thu, 21 Apr 2016 19:31:30 GMT

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?

Re: Sum Date Range

Anonymous — Thu, 21 Apr 2016 20:03:41 GMT

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!!