topic Re: Selecting items between dates ranges in QlikView

Selecting items between dates ranges

Thu, 29 May 2014 13:10:45 GMT

Hi,

So I've got a table similar to the following, where an individual goes through 3 stages but the amount of time spent at a stage and when they join are likely to change.

Here we have 3 guys who joined at various dates and it shows their progression through the stages.

name	stage	Start Dt	End Dt
john	stage1	Jun-10	Dec-10
john	stage2	Jan-11	Dec-11
john	stage3	Jan-12	Mar-13
paul	stage1	Jan-11	Jun-12
paul	stage2	Jul-12	Dec-12
paul	stage3	Jan-13	Apr-13
Peter	stage1	Jan-10	Jan-12
Peter	stage2	Feb-12	Feb-13
Peter	stage3	Mar-13	Dec-13

What I'm trying to do is by selecting a single date value in a calendar, find out how many people were at each stage

so the output would be if Feb 2012 was selected as a date

Feb-12
	stage1	1
	stage2	1
	stage3	1

Nov-10

stage 1

stage 2

stage 3

as John and Peter are in stage 1 but Paul hasn't commenced yet

With a calendar set up to = variable vDate

and stage as a dimension , I thought the follwing would work

=count({<vDate= {">=$(START_DT) <= $( END_DT)"} >} Stage)

but regardless of what im putting in its just counting the totals each time to give

stage 1	3
stage 2	3
stage 3	3

any ideas please

Re: Selecting items between dates ranges

Thu, 29 May 2014 13:15:46 GMT

Are they true date fields or are they text?

Re: Selecting items between dates ranges

Thu, 29 May 2014 13:19:40 GMT

they'll be true date fields imported from a sql table and are currently in this format

START_DT END_DT

2004-07-01 00:00:00.000 2010-07-31 00:00:00.000

Re: Selecting items between dates ranges

Thu, 29 May 2014 13:22:36 GMT

I would suggest reformatting them on load to match your DateFormat (set at start of load script) this will make it much easier to work with and should help solve your issue

Re: Selecting items between dates ranges

Thu, 29 May 2014 13:26:39 GMT

Sorry , the excel above is a basic premise of the problem and getting the data in isn't an issue as I can manipulate it via sql before import if required. The date format aside I'm trying to get context around the expression being incorrect

Re: Selecting items between dates ranges

lironbaram — Thu, 29 May 2014 13:48:56 GMT

have a look at the attach example

it uses qlikview interval match script function

what i did

is to create a date list between the min and max date

then using interval match

each record in the base table will be connected to the months relevant to him

Re: Selecting items between dates ranges

Thu, 29 May 2014 13:49:40 GMT

Apologies you would do your set analysis vs the fields not the variable i.e.

count({$<START_DT={">=$(vDate)"},END_DT={"<=$(vDate)"}>} Stage)

Re: Selecting items between dates ranges

Thu, 29 May 2014 13:51:33 GMT

Just working it through now

Re: Selecting items between dates ranges

Thu, 29 May 2014 14:16:59 GMT

felim : I like the neatness of the answer but that formula isn't quite working

but if i do either of the following

count({$<START_DT={">=$(vDate)"}>} Stage)

count({$<END_DT={"<=$(vDate)"}>} Stage)

they work fine

liron : that works well but on looking at the table map, it generates a number of synthetic keys . Would that be an issue?

Re: Selecting items between dates ranges

Thu, 29 May 2014 14:22:06 GMT

Sum(IF(START_DT>=$(vDate) AND END_DT<=$(vDate),1,0))