Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Selecting items between dates ranges

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.

namestageStart DtEnd Dt
john stage1Jun-10Dec-10
john stage2Jan-11Dec-11
john stage3Jan-12Mar-13
paulstage1Jan-11Jun-12
paulstage2Jul-12Dec-12
paulstage3Jan-13Apr-13
Peterstage1Jan-10Jan-12
Peterstage2Feb-12Feb-13
Peterstage3Mar-13Dec-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
stage11
stage21
stage31

if

Nov-10

stage 1

2

stage 20
stage 30

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 13
stage 23
stage 33

any ideas please

1 Solution

Accepted Solutions
lironbaram
Partner - Master III
Partner - Master III

hi

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

View solution in original post

9 Replies
Not applicable
Author

Are they true date fields or are they text?

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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

lironbaram
Partner - Master III
Partner - Master III

hi

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

Not applicable
Author

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

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

Not applicable
Author

Just working it through now

Not applicable
Author

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?

Not applicable
Author

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