8 Replies Latest reply: Aug 27, 2015 2:43 PM by Darrin Pilkington

# Single date variable between two dates in set analysis question

All the samples of date range in set analysis I find show a similar format of a single date record between two date variables selected like here.

Sum({< CalendarDate = {'>=\$(=Date(vStartDate))<=\$(=Date(vEndDate))'} >} SalesAmount)

I have Start and End record dates and need to have a single date variable range.

Example:

Sum({< vDateVariable = {' >= (RecordStart)  < (RecordEnd) '} SalesAmount)

All the ways I have tried with the RecordStart and RecordEnd dates not recognize the columns once in the set.

• ###### Re: Single date variable between two dates in set analysis question

Assuming that RecordStart and Record End date are field, then you should write

Sum({< RecordStart = {"<=\$(vDateVariable)"}, RecordEnd={">\$(vDateVariable)"}>} SalesAmount)

Celambarasan

• ###### Re: Single date variable between two dates in set analysis question

Hi Darrin,

Try to add a = before it

Sum({< vDateVariable = {' >= (=RecordStart)  < (=RecordEnd) '} SalesAmount)

The better way is to use the variable for comparing, comparing the the fields my not yield a correct result

• ###### Re: Single date variable between two dates in set analysis question

Hi,

what is is your expression for vDateVariable ??

try this

Sum({< vDateVariable = {">= \$(=RecordStart)  < \$(=RecordEnd)"} SalesAmount)

Regards

• ###### Re: Single date variable between two dates in set analysis question

Hi Max,

The vDateVariable is generated from a calendar object.  When I look at the value created it is a numeric value.

ie. 42127 which = 5/3/2015

All formats I have tried give me an error.

• ###### Re: Single date variable between two dates in set analysis question

I have tried every variation and return either 0 or null.

I have a calendar object which places the numeric value for a date into the variable  vActiveDt

From there I have tried the following. Both iterations below with and without wrapping the variable in Date()

Sum({< FeatureStartDt = {"<=Date(\$(vActiveDt))"}, FeatureEndDt={">Date(\$(vActiveDt))"}>} LOSCnt)

Sum({< FeatureStartDt = {"<=\$(vActiveDt)"}, FeatureEndDt={">\$(vActiveDt)"}>} LOSCnt)

Sum({< Date(vActiveDt) = {">= \$(=FeatureStartDt)  < \$(=FeatureEndDt)"} LOSCnt)

Sum({< vActiveDt = {">= \$(=FeatureStartDt)  < \$(=FeatureEndDt)"} LOSCnt)

Thank you.

• ###### Re: Single date variable between two dates in set analysis question

Hello,

I have attached a sample of what I am working with and trying to do.

Thank you.

• ###### Re: Single date variable between two dates in set analysis question

=count( {\$ <pStartDt={"<=\$(=date(vActiveDt))"},pEndDt={">=\$(=date(vActiveDt))"}>} %LOSKey)

PFA

• ###### Re: Single date variable between two dates in set analysis question

Massimo,

Thank you very much.