Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
cmclear22
Contributor
Contributor

Date Range in Set Analysis

Hi all, 

I'm trying to something I thought would be pretty straightforward but just can't seem to crack this. I would like to sum up Sales for a fixed date range.

I'm ok with getting sales for a single date: sum({$<SaleDate={'1/23/2010'}>}Sales) and sales greater than a sale date sum({$<[SaleDate]={">=1/31/2019"}>}Sales).

But I can't figure out how to get two dates into my set statement say, a start date of 1/31/2019 and an end date of 2/15/2019 so as to get a sum of sales for all dates between the start and end sales dates...Any help would be much appreciated. I've checked a ton of posts but can't find a solution on this...

Clearly, this won't work...sum({$<[SaleDate]={">=1/31/2019", "<=2/15/2019", }>}Sales)

Also, I'd like to define a variable for all sales dates in a fixed month, say, January 2019 based on my SaleDate dimension. Not having any luck with this either. 

Many thanks, 

Chris

1 Solution

Accepted Solutions
Taoufiq_Zarra

@cmclear22  My be :

Sum({<SaleDate={"$(= '>=' & '31/1/2019' & '<=' & '15/2/2019')"}>} Sales)

or you can use variable like :

vStartDate: date#('1/31/2019','M/D/YYYY')

vEndDate: date#('2/15/2019','M/D/YYYY')

The expression will be:

sum({<SaleDate={'>=$(vStartDate)<=$(vEndDate)'}>} Sales)

 

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

1 Reply
Taoufiq_Zarra

@cmclear22  My be :

Sum({<SaleDate={"$(= '>=' & '31/1/2019' & '<=' & '15/2/2019')"}>} Sales)

or you can use variable like :

vStartDate: date#('1/31/2019','M/D/YYYY')

vEndDate: date#('2/15/2019','M/D/YYYY')

The expression will be:

sum({<SaleDate={'>=$(vStartDate)<=$(vEndDate)'}>} Sales)

 

Regards,
Taoufiq ZARRA

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

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