Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
if
Nov-10 | stage 1 | 2 |
stage 2 | 0 | |
stage 3 | 0 |
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
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
Are they true date fields or are they text?
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
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
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
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
Apologies you would do your set analysis vs the fields not the variable i.e.
count({$<START_DT={">=$(vDate)"},END_DT={"<=$(vDate)"}>} Stage)
Just working it through now
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?
Sum(IF(START_DT>=$(vDate) AND END_DT<=$(vDate),1,0))