Date Range with Set Analysis

[Roop]

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).

[#2]

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 · Accepted solution]

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]

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 !