topic Nested Set Analysis question in QlikView

Nested Set Analysis question

Mon, 09 Jan 2012 22:59:05 GMT

Hi I was debuging an issue with some set analysis and found out that when I use fields on inner expressions QV does not like it, here is an example, SLA is a field and QV does not figures it out when I use is like the following:

Count
(
{$<
IsAbandon ={0},
ContactIDSkillNo ={"=Sum({$<test={1}>}Duration) < (SLA*1000)"}

>}
DISTINCT ContactID
)

But when I use a straight number, it works:

Count
(
{$<
IsAbandon ={0},
ContactIDSkillNo ={"=Sum({$<test={1}>}Duration) < (30*1000)"}

>}
DISTINCT ContactID
)

I tried to replace the expression with:

$=(SLA)

max({}SLA)

num(SLA)

Any idea on how can I use a field inside the string so it can work?

Thanks in advande for you time.

-ed

Nested Set Analysis question

swuehl — Mon, 09 Jan 2012 23:29:08 GMT

Does your field SLA only have one single value?

The set expression is only evaluated once, not per record / dimension value (if you use the set expression in a chart), so if SLA is ambiguous, QV won't know which value to put in.

Nested Set Analysis question

Mon, 09 Jan 2012 23:36:50 GMT

Thanks for the help swuehl, SLA has multiple values, but since I am using it inside a Pivot table chart, I asumed that each one of the subgroups will only evaluate its own subset of data, and then the value in SLA field would be unique, for example:

If the hierarchy that the pivot tale is the following:

Dimension1 Expression1

Customer Count(....)

Then I was expecting that the Dimension1 will spilt the data and when Expression1 will execute against the subset of data, since SLA as a 1 to 1 relation with Customer then the value for SLA when evaluated will be unique.

That's why I dont know why its not working.

-ed

Nested Set Analysis question

swuehl — Mon, 09 Jan 2012 23:51:48 GMT

Try something like:

Count
(
{$<
IsAbandon ={0},
ContactIDSkillNo ={$(=concat(aggr(if(Sum({$<test={1}>}Duration) < (SLA*1000),ContactIDSkillNo),Customer),','))}

>}
DISTINCT ContactID
)

Not sure if this is better performing than not using a set analysis, i.e. traditional record based comparison (probably also using aggr() ) instead. I hope I haven't forgot a bracket or so, please check.

--Stefan

Nested Set Analysis question

rlp — Thu, 26 Jan 2012 12:33:44 GMT

If the field SLA has only one value for each value of the dimension, you could perhaps try Only(SLA) instead of SLA .

Nested Set Analysis question

Thu, 26 Jan 2012 16:30:22 GMT

Thanks for the suggestion Richard, I'll see if that works on my scenario.

Thanks again.

-ed