Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Sum Date Range

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

   

FundCompanyMy_DateTypeOriginal AmountSum Range
ABCDEFG7/1/2015Cost-1,000,000-1,000,000
ABCDEFG7/2/2015Cost-2,000,000-2,000,000
ABCDEFG8/1/2015Proceeds750,000750,000
ABCDEFG8/7/2015Proceeds5,0005,000
ABCDEFG8/14/2015Proceeds1,250,0001,250,000
ABCDEFG9/4/2015Proceeds1,0001,000
ABCDEFG9/18/2015Proceeds1,000,0001,000,000
ABCDEFG05/01/2016Cost01,000
ABCDEFG05/01/2016EMV01,000
ABCDEFG05/01/2016Proceeds06,000
ABCDEFG05/02/2016Cost02,000
ABCDEFG05/02/2016EMV02,000
ABCDEFG05/02/2016Proceeds07,000

Any inputs will greatly appreciated!

Thanks,

Frank

1 Solution

Accepted Solutions
sunny_talwar

See if one of the two is what you want

View solution in original post

16 Replies
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
Not applicable
Author

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

See if one of the two is what you want

Anonymous
Not applicable
Author

Thanks again Sunny!!..

I missed the sum infront of the Aggr.

sunny_talwar

Yup

Anonymous
Not applicable
Author

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

   

FundCompanyMy_DateTransTypeAmountRange Sum (1)
ABCDEFG7/1/2015Cost-1,000,000-1,000,000
ABCDEFG7/2/2015Cost-2,000,000-2,000,000
ABCDEFG8/1/2015Proceeds750,000750,000
ABCDEFG8/7/2015Proceeds5,0005,000
ABCDEFG8/14/2015Proceeds1,250,0001,250,000
ABCDEFG9/4/2015Proceeds1,0001,000
ABCDEFG9/18/2015Proceeds1,000,0001,000,000
ABCDEFG05/01/2016Cost00
ABCDEFG05/01/2016EMV010
ABCDEFG05/01/2016Proceeds00
ABCDEFG05/02/2016Cost00
ABCDEFG05/02/2016EMV010
ABCDEFG05/02/2016Proceeds00
ABCDEFG05/03/2016Cost00
ABCDEFG05/03/2016EMV010
ABCDEFG05/03/2016Proceeds00
ABCDEFG05/04/2016Cost00
ABCDEFG05/04/2016EMV010
ABCDEFG05/04/2016Proceeds00
ABCDEFG05/05/2016Cost00
ABCDEFG05/05/2016EMV010
ABCDEFG05/05/2016Proceeds00
ABCDEFGTotal 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?