Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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
Thank you Oleg,
I was hoping to acompish this without modifying the script, but looks like i'll have too.
Regards,
-Sergey