topic Date Range with Set Analysis in QlikView

Date Range with Set Analysis

Roop — Wed, 10 Feb 2010 16:16:02 GMT

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

Date Range with Set Analysis

Wed, 10 Feb 2010 16:39:18 GMT

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

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

Date Range with Set Analysis

Miguel_Angel_Baeyens — Wed, 10 Feb 2010 16:39:48 GMT

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.

Date Range with Set Analysis

Roop — Wed, 10 Feb 2010 17:25:09 GMT

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 !