Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator 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
Highlighted
MVP
MVP

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

PFA

View solution in original post

9 Replies
Highlighted

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

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



Celambarasan

Highlighted

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 

Highlighted

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.
Highlighted
Creator II
Creator II

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.

Highlighted
Creator II
Creator II

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.

Highlighted
Creator II
Creator II

Hello,

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

Thank you.

Highlighted
MVP
MVP

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

PFA

View solution in original post

Highlighted
Creator II
Creator II

Massimo,

Thank you very much.

Highlighted
Contributor II
Contributor II

 

Maxgro,

As it would be adding a variable.
Variavel brings value.

Variable: vExpressao1

Sum({<DATA={'>$(=Date(vFromDate))<$(=Date(vToDate))'}>}vExpressao1)