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

Single date variable between two dates in set analysis question

Anonymous — Wed, 03 Jun 2015 07:14:32 GMT

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.

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

CELAMBARASAN — Wed, 03 Jun 2015 07:19:44 GMT

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

avinashelite — Wed, 03 Jun 2015 07:21:22 GMT

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

PrashantSangle — Wed, 03 Jun 2015 07:34:56 GMT

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

Anonymous — Thu, 25 Jun 2015 03:08:45 GMT

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

Anonymous — Fri, 14 Aug 2015 20:53:49 GMT

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

Anonymous — Fri, 14 Aug 2015 21:21:58 GMT

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

maxgro — Sun, 16 Aug 2015 14:21:46 GMT

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

PFA

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

Anonymous — Thu, 27 Aug 2015 18:43:16 GMT

Massimo,

Thank you very much.

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

EmmaMark — Sun, 26 Apr 2020 12:49:06 GMT

Maxgro,

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

Variable: vExpressao1

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