Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I've come across a tricky set analysis operation.
I have two tables (refer to sample app for further details):
t_fact:
LOAD * INLINE [
Dimension A, Measure #1, Measure #2
F, 6, 1
F, 5, 2
G, 4, 3
G, 3, 4
H, 2, 5
H, 1, 6
];
t_dataisland:
LOAD * INLINE [
Measure #3, Measure #4, Measure #5
11, 3, 10
7, 7, 20
3, 11, 30
];
I want to create a simple straight table with Dimension A as Dim and a single expression which should be the sum of [Measure #5). Given that there is no connection between tables I want to use Set Analysis in a way similar to this:
= Sum({<[Measure #3]={"$(=Sum([Measure #1]))"}, [Measure #4]={"$(=Sum([Measure #2]))"}>}[Measure #5])
In fact, what I'm saying is that given the sum of Measures #1 and #2, retrieve the value of Measure #5:
Dimension A | Measure #5 |
---|---|
F | 10 |
G | 20 |
H | 30 |
I am missing something because I can't get it to work... maybe you'll have better clues than I do.
Thanks in advance.
Although won't give you an amazing performance, but this expression works:
=Sum(Aggr(If([Measure #3] = Sum([Measure #1]) and [Measure #4] = Sum([Measure #2]), [Measure #5]), [Dimension A], [Measure #3], [Measure #4], [Measure #5]))
Although won't give you an amazing performance, but this expression works:
=Sum(Aggr(If([Measure #3] = Sum([Measure #1]) and [Measure #4] = Sum([Measure #2]), [Measure #5]), [Dimension A], [Measure #3], [Measure #4], [Measure #5]))
It certainly works.
I am amazed that this can't be achieved with set analysis.
Thank you for your help.