topic Link between table through Set Analysis in QlikView

Link between table through Set Analysis

Tue, 08 Mar 2011 11:02:44 GMT

Hello,

I am dealing with support incident; each incident has 2 dates information (OpenMonth & ResolvedMonth)

I have created 2 tables to ease the calculation of some statistics (such as YTD, YTD-1, M and M-1)

Script to create the 1st table for the OpenMonth variable is: http://community.qlik.com/forums/t/41759.aspx

Then to create the 2nd one, I simply do:

ResovedAsOf:
LOAD
"As of OpenMonth" as "As of ResolvedMonth"
,"OpenMonth Range" as "ResolvedMonth Range"
,"OpenMonth" as "ResolvedMonth"
RESIDENT OpenAsOf;

On top of that, I have created a slider, with following data:

Slider:
Load MonthName(Today(),-IterNo()+1) as SliderMonth AutoGenerate(10)
while IterNo() < 10

So in my different graph, wether I deal with OpenMonth or ResolvedMonth, I use a SetAnalysis as following:

=count({$<[As of ResolvedMonth]=SliderMonth>} SD)

or =count({$ <OpenMonth = {"=NUM(OpenMonth) <= NUM(SliderMonth)"}>}SD)

Now, I want to create a summary table which look like this:

Because, for tickets submitted, I need to deal with OpenMonth, and for tickets closed, I need to deal with ResolvedMonth,

I don't think I can use either "OpenedMonth Range" or "ResolvedMonth Range" as the dimension,

So I have created another table (which only contains the list of statistics YTD, YTD-1, M, M-1), and use "Range" as a dimension

Range:
Load Distinct "OpenMonth Range" as Range
RESIDENT OpenAsOf;

Now I can't figure out how to make the "link" between the dimension "Range"and the different expression.

For the moment, I do have

for tickets submitted: count( { $ < [As of OpenMonth] = SliderMonth>} [SD]), but where do I mention that [OpenedMonth Range] should be equal to [Range]?

Thank in advance,

Link between table through Set Analysis

Tue, 08 Mar 2011 11:06:17 GMT

from what I understood from the set analysis; I would write:

count( { $ < [As of OpenMonth] = SliderMonth,[OpenMonth Range]={"Range"}>} [SD])

But I can't make it work. Looking forward your help! Thanks

Link between table through Set Analysis

Wed, 09 Mar 2011 03:30:16 GMT

(On top of linking 2 different fields to a same dimension)

This is my output target. I am confused regarding what I need to use as expression or dimension.

Perf is = Ticket closed / ticket opened

%Growth = (M - [M-1])/M

I want to make sure that %Growth of perf is calculated as (Perf(M) - Perf(M-1))/Perf(M-1)

and not %Growth(Closed)/%Growth(Opened)

Link between table through Set Analysis

Wed, 09 Mar 2011 11:32:56 GMT

I have the answer to my first question:

For Ticket open: count({$<[As of OpenMonth]=SliderMonth>} if([OpenMonth Range]="Range",SD))

For Ticket closed: count({$<[As of ResolvedMonth]=SliderMonth>} if([ResolvedMonth Range]="Range",SD))

Then for Perf: [Ticket closed]/[Ticket open]

Remaining task:

How to add in my dimension a new member %growth?