Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Still a newbie, not necessarily looking for an answer per se .... just pointers on where to begin looking !
Here's my scenario....
My main data set <items> is 1 row per item, PK = [ID]
Secondary data set is a <changelog> :- [ID], [Status], [DateTimeFrom], [DateTimeTo], [duration]
And the value questions I'm trying to answer with QV are ...
(1) Number of items in [Status] 'E' on the 1st of each Month.
This one I have done by first creating reference date tables, and then making a chart with Dimension [ReferenceDate] and expression of "count( {<[Status]={'E'}>} DISTINCT ID) "
.. all good so far !
Now the tricky part ....
(2) For the main data set items identified in (1), sum the [duration] of all <changelog> entries, where [status]='B' and [DateTimeTo] < [ReferenceDate]
... so here I am basically using the results of (1) and trying to use them as a filter on a further subsequent query.
I am at a loss as to how to link [ID] from (1), and use it as a filter on (2).
Any ideas ??
Can you upload your sample apps?
I don't have a sample just at the moment, am working from QVDs. I will try to make a sample today.
So I have 2 incoming datasets.
[Worklist] holds 1 row per item, with key field [ID]
[StatusWindows] is a change log recording the status of each [ID] with start & end DateTime.
For each month, I need to report 2 facts.
(1)
How many [ID]s had [SatusDpt]='DST' & [Status]='In Progress'
This I have done by creating a flag for those start/end times that cover the 1st of a Month
Count({<StatusDpt = {'DST'},Status = {'In Progress'},ReferenceDate_TH_flag = {'Y'}>} ID)
(2)
For each of the [ID]s in the above result,
How many days did they previously have : [StatusDpt]='DST' and [Status]='In Queue'
In the example, I have sumed the number of days thus :
rangesum(above(sum( {<StatusDpt={'DST'},Status={'In Queue'}>} DaysDuration), 0 , RowNo()))
However, it is displaying a result for Aug.Sept.Oct
Although I want the entire cumulative total as shown, I only want it to display these results when there is also a result in (1).
(so November onwards for [ID]=3)