Sum of Values in a Date Range

[qlikuser12]

Hello,

I am trying to sum a set of values based on a range of dates. Looking through other posts I came up with the following expression: 

=Sum({<[GRANTS_SUMMARY.Award Date]={'>=$(10/1/2020)<=$(9/30/2021)'}>} [Open Awards])

In other words, wanting to sum Open Awards only if the corresponding GRANTS_SUMMARY.Award Date falls within the date range.

Anyone have any idea what I'm doing wrong?

[QFabian]

Hi @qlikuser12 , check the quotes  ' " symbols.

maybe its like , this, but you try please,check this in qlik help about it

Another option is to use variables with the dates.

=Sum({<[GRANTS_SUMMARY.Award Date]={">=10/1/2020<=9/30/2021"}>} [Open Awards])

https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/SetAnalys...

Quotes and case sensitivity

If the values contain blanks or special characters, the values need to be quoted. Single quotes will be a literal, case-sensitive match with a single field value. Double quotes imply a case-insensitive match with one or several field values. For example:

• <Country = {'New Zealand'}>

  Matches New Zealand only.

• <Country = {"New Zealand"}>

  Matches New Zealand, NEW ZEALAND, and new zealand.

Dates must be enclosed in quotes and use the date format of the field in question. For example:

• <ISO_Date = {'2021-12-31'}>

• <US_Date = {'12/31/2021'}>

• <UK_Date = {'31/12/2021'}>

Double quotes can be substituted by square brackets or by grave accents.

https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/SetAnalys...

xample: Chart expressions for set modifiers with dollar-sign expansions

Example - chart expressions

Examples	Results
sum( {$<Year = {$(#vLastYear)}>} Sales )	Returns the sales for the previous year in relation to current selection. Here, a variable vLastYear containing the relevant year is used in a dollar-sign expansion.
sum( {$<Year = {$(#=Only(Year)-1)}>} Sales )	Returns the sales for the previous year in relation to current selection. Here, a dollar-sign expansion is used to calculate previous year.