topic Re: Nested Chart Queries ? in QlikView

Nested Chart Queries ?

simonb2013 — Mon, 17 Feb 2014 12:06:56 GMT

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 ??

Re: Nested Chart Queries ?

MK_QSL — Mon, 17 Feb 2014 12:27:38 GMT

Can you upload your sample apps?

Re: Nested Chart Queries ?

simonb2013 — Tue, 18 Feb 2014 10:43:53 GMT

I don't have a sample just at the moment, am working from QVDs. I will try to make a sample today.

Re: Nested Chart Queries ?

simonb2013 — Wed, 19 Feb 2014 09:14:11 GMT

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)