Connect data island via expression

[#1]

I have a complete data model with fact and dimensions. This model uses section access to reduce the data. I have an aggregated table that is completely unconnected to my model. This is used to prevent the data reduction to take place on that table. I am not succeeding in connecting the table via an expression.

Below you will find a simplified example of my problem.

Revenue:
LOAD * INLINE [
    Week number, Store, Department, Revenue
    1, Adam, Pants, 25
    1, Eve, Skirts, 15
    1, Eve, Hats, 20
    2, Adam, Sweater, 55
    2, Adam, Pants, 50
    2, Eve, Skirts, 35
    3, Adam, Shoes, 25
    3, Eve, Hats, 40
];
Goal:
LOAD * INLINE [
    _Week number, _Store, Goal
    1, Adam, 30
    1, Eve, 20
    2, Adam, 120
    2, Eve, 30
    3, Adam, 25
    3, Eve, 35
];

I want to create the following table with selection options:

The goal should give the goal for each week and each particular store. It should also respond to the selections I make in the listboxes.

I have attached the qvw as an example.

What is the best way to handle this?

[lironbaram · Accepted solution]

hei

attach is an example

hope it helps you

[#3]

That works Liron, thank you. But I actually have a model that looks something like this:

Does this mean that I have to extend the Goal table with the Week number and Store fields in order to make it work? Or are there other ways to do it? I would like to keep my model as clean as possible.

(See attached qvw)

[#4]

Is there anybody that can tell me what the best way to tackle the described problem is? I had oversimplified my example a bit. So the second one matches my actual situation a lot better.

Thanks

[Miguel_Angel_Baeyens]

Hi,

It seems that your tables where not properly populated. The If() solution works, check the attached application.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#6]

Hhhhm that's weird.

I have added a distinct to the IF to get the results right:

SUM(DISTINCT IF(%Store_key = _%Store_key AND %Week_key = _%Week_key, Goal))

But you are right, it does work with the if construction.

Thanks