Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set analysis - data island

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 AMeasure #5
F10
G20
H30

I am missing something because I can't get it to work... maybe you'll have better clues than I do.

Thanks in advance.

1 Solution

Accepted Solutions
sunny_talwar

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]))


Capture.PNG

View solution in original post

2 Replies
sunny_talwar

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]))


Capture.PNG

Not applicable
Author

It certainly works.

I am amazed that this can't be achieved with set analysis.

Thank you for your help.