Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
mrbqlik2009
Contributor III
Contributor III

Calculate sum before a specific date

hi,

suppose i have this table in database.

NameDateamount
x1/15/202020
x1/20/202027
x1/23/202015
y2/1/202045
y2/5/202031

 

i want calculate sum(amount) of records with name 'x' and before date for example 01/22/2020 in expression in a chart.

for example the answer become 20+27=47

show 47.

Labels (2)
2 Solutions

Accepted Solutions
Taoufiq_Zarra

Maye be :

=sum({$<Name={'x'},Date={"<=$(=Date#('1/22/2020','M/DD/YYYY'))"}>} amount)

 

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

Taoufiq_Zarra

no but

=Date#('2020/01/01','YYYY/MM/DD')

Date#

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

5 Replies
Taoufiq_Zarra

Maye be :

=sum({$<Name={'x'},Date={"<=$(=Date#('1/22/2020','M/DD/YYYY'))"}>} amount)

 

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
mrbqlik2009
Contributor III
Contributor III
Author

thank you for reply.

I put specific date in a variable. 

suppose Date and specific date is not in format 'MM/DD/YYYY'.

how can i calculate both in this format.

Taoufiq_Zarra

@mrbqlik2009 

you can change Date Format in 'MM/DD/YYYY' to another 

bellow with variable :

Taoufiq_ZARRA_0-1590161111546.png

 

=sum({$<Name={'x'},Date={"<=$(=vDate)"}>} amount)

 

Taoufiq_ZARRA_1-1590161160343.png

 

 

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
mrbqlik2009
Contributor III
Contributor III
Author

can put Date#('01/01/2020','YYYY/MM/DD') in expression?

Taoufiq_Zarra

no but

=Date#('2020/01/01','YYYY/MM/DD')

Date#

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉