Sum of others using set analysis

[#1]

I have found myself in need of an interesting formula that I think is best handled by set analysis though I'm not sure how it could be constructed. I consider myself fairly adept at set analysis so hopefully I'm not making any assumptions in my explanation.

What I need is a way to get the total of all other dimensions by dimension value. In a simple environment I would use Sum(Total Value) - Sum(Value) to get the desired number.

I immediately thought of using indirect set analysis but the method of exclusion was problematic. How do you compare a field against itself? Sum({$<Value = {'=Dimension <> Dimension'}>} Total Value) presents some obvious problems.

Any suggestions would be greatly appreciated!

[johnw]

So does sum(total Value) - sum(Value) give the right number? If so, I'd stick with that. QlikView SHOULD be smart enough to only do sum(total Value) once, and then just use the result on every row. So there would be no performance benefit that I can think of to using set analysis.

And if the simple sum() approach does work, then set analysis WON'T work. A set is only constructed once, for the entire chart. You don't get a separate set for each row of the chart. So there's no way to refer to the current value of the dimension for that row of the chart. (Unless it's a new feature I haven't noticed yet.)

[#3]

Thanks for the feedback John!

I would think that performing one sum instead of two would create a performance increase. Obviously this wouldn't be a big concern if I was performing a simple sum, but the actual calculation is much longer and performance is a concern in the real scenario.

While I agree that the set is only constructed once, I have been able to trick QV into making it work dynamically using indirect sets. When using indirect sets, I have been able to effectively get a separate set for each row by creating a true/false value from the intended set result. That said, the real question is exactly how you put it; can I reference the dimension in the set? It seems like it should be possible using the indirect method though I can't get the syntax if it is possible.

[johnw]

Aaron wrote:I have been able to trick QV into making it work dynamically using indirect sets. When using indirect sets, I have been able to effectively get a separate set for each row by creating a true/false value from the intended set result.

Sounds like you're ahead of me, then. Can you give an example of how this works? I'd like to fiddle with it, and I'm having a hard time picturing what you mean. 🙂