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: 
Roop
Specialist
Specialist

Date Range with Set Analysis

I seem to have hit a brick wall with this one. I have looked at a whole series of posts - none of which seem to assist.

I have a simple problem in that i wish to retrieve sum between 2 dates in the following expression:

Sum( {$<TIME_DATE = {"<$(weekstart(today(1))) >=$(yearstart(today(1)))"}>} REVENUE )

where the result is needed for the 1st day of the year through to the last day of last week (as we are always looking retrospectively 1 week in arrears).

1 Solution

Accepted Solutions
Miguel_Angel_Baeyens

Hello,

It seems to have a little syntax error in the set, I'd use

Sum( {$<TIME_DATE = {"<$(=date(weekend(today(1), -1)))>=$(=date(yearstart(today(1))))"}>} REVENUE )
(note the equal sign between opening parentheses and date function name. Except for that, it looks like correct to me.

Of course, TIME_DATE must be a date field properly fromatted in the load from the source.

Hope this helps.

View solution in original post

3 Replies
Not applicable

I think you just need equals signs in your dollar sign expansions:

Sum( {$<TIME_DATE = {"<$(=weekstart(today(1))) >=$(=yearstart(today(1)))"}>} REVENUE )


Miguel_Angel_Baeyens

Hello,

It seems to have a little syntax error in the set, I'd use

Sum( {$<TIME_DATE = {"<$(=date(weekend(today(1), -1)))>=$(=date(yearstart(today(1))))"}>} REVENUE )
(note the equal sign between opening parentheses and date function name. Except for that, it looks like correct to me.

Of course, TIME_DATE must be a date field properly fromatted in the load from the source.

Hope this helps.

Roop
Specialist
Specialist
Author

Excellent answer! I have tried so many different things - even that one but the key was to ensure that the date field was properly formatted!

Thanks you Miguel !

Big Smile