Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

set analysis - creating calculated dimention

I'm trying to create a calculated dimention and having an issue with getting my set analysis to work. Any feedback is greatly apreciated.

What i need to do is to display [Agency Name] for any time row in a orders table that has "(Begin+New) <> End" condition.

Table look like this:

Header 1Header 2Header 3Header 4Header 5
Row IDAgency NameBeginNewEnd
1Agency1527
2Agency1718
3Agency18311
4Agency2123
5Agency2325

etc...

This is what I have now and it works:

=aggr(

IF(Count( {$<[Begin] = {'<>[End]'} >} [Row ID]) > 0,[Agency Name])

, [Agency Name])

How can I do something like this (Begin + New)?

=aggr(

IF(Count( {$<([Begin]+[New]) = {'<>[End]'} >} [Row ID]) > 0,[Agency Name])

, [Agency Name])

Thank you!

1 Solution

Accepted Solutions
MVP
MVP

set analysis - creating calculated dimention

Unfortunately, you can't use Set Analysis this way, for a few reasons. The main problem is that the Set Analysis condition is being validated once for the whole chart, not for each row.

If your data is small enough, you can get by with the calculated dimension the way you created it. It will be slower than it shoudl be...

The best way of solving this problem is creating a flag in the load script:

LOAD

...

IF (Begin + New <> End, 1, null()) as AgencyFlag

...

Then your expression could simply look like this:

sum({<AgencyFlag={1}>} Whatever)

I'd also recommend implementing it in the Expression and not in the Calculated Dimension, because calculated dimensions are much heavier than expressions, in terms of performance.

best,

Oleg

2 Replies
MVP
MVP

set analysis - creating calculated dimention

Unfortunately, you can't use Set Analysis this way, for a few reasons. The main problem is that the Set Analysis condition is being validated once for the whole chart, not for each row.

If your data is small enough, you can get by with the calculated dimension the way you created it. It will be slower than it shoudl be...

The best way of solving this problem is creating a flag in the load script:

LOAD

...

IF (Begin + New <> End, 1, null()) as AgencyFlag

...

Then your expression could simply look like this:

sum({<AgencyFlag={1}>} Whatever)

I'd also recommend implementing it in the Expression and not in the Calculated Dimension, because calculated dimensions are much heavier than expressions, in terms of performance.

best,

Oleg

Not applicable

set analysis - creating calculated dimention

Thank you Oleg,

I was hoping to acompish this without modifying the script, but looks like i'll have too.

Regards,

-Sergey

Community Browser