2 Replies Latest reply: May 7, 2012 2:04 PM by Sergey Pinchuk

# 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:

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!

• ###### 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:

...

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

• ###### 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