set analysis - creating calculated dimention

[#1]

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 1	Header 2	Header 3	Header 4	Header 5
Row ID	Agency Name	Begin	New	End
1	Agency1	5	2	7
2	Agency1	7	1	8
3	Agency1	8	3	11
4	Agency2	1	2	3
5	Agency2	3	2	5

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!

[Oleg_Troyansky · Accepted solution]

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

[#3]

Thank you Oleg,

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

Regards,

-Sergey