Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikuser12
Contributor
Contributor

Sum of Values in a Date Range

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?

Labels (2)
1 Reply
QFabian
MVP
MVP

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

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.

Greetings!! Fabián Quezada (QFabian)
did it work for you? give like and mark the solution as accepted.