Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
darrin_pilkingt
Contributor II

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.

Thanks for your help.

1 Solution

Accepted Solutions
MVP
MVP

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

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

PFA

8 Replies

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

Great dreamer's dreams never fulfilled, they are always transcended.
darrin_pilkingt
Contributor II

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.

darrin_pilkingt
Contributor II

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.

darrin_pilkingt
Contributor II

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.

MVP
MVP

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

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

PFA

darrin_pilkingt
Contributor II

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

Massimo,

Thank you very much.

Community Browser