4 Replies Latest reply: Aug 13, 2014 2:47 AM by Stevens Bonnand

# Sum values in function of dates

Hi everyone,

I need some helps to resolve this problem :

I got 2 tables :

Table A :

Key          Date1          Value1

1               2010          5

2               2010          8

3               2011          7

4               2012          6

5               2013          2

Table 2 :

Key          Date2          Value2

1               2013          12

2               2011          10

3               2010          1

4               2010          7

5               2011          6

Resulat needed :

Date          Value1          Value2

2010          5+8               1+7

2011          7                   10+6

2012          6                   0

2013          2                   12

I need to sum value1 and value2 in function of year (from field Date1 and Date2). For this, I need to "ignore" the Key. Can I do it into an expression ? I can't modify my datamodel. I need to keep the key and I can't use date fields as key.

Thanks for helping

EDIT : Easy thank IF but very slow. I'm looking for SET ANALYSIS

• ###### Re: Sum values in function of dates

Can you make your dimension for dates to be Date for both instead of Date1 and Date2?

if so make  a straight table with date as a dimension, then sum(Value1) and sum(Value2) for the expressions

• ###### Re: Sum values in function of dates

In the file you have my proposal

• ###### Re: Sum values in function of dates

Hi,

Try like this

Data:

LOAD

Key,

Date1 AS Date,

Value1

FROM TableA;

Concatenate(Data)

LOAD

Key,

Date2 AS Date,

Value2

FROM TableB;

Now in Straight table use

Dimension: Date

Expression : Value1 = Sum(Value1)

Value2 = Sum(Value2)

Hope this helps you.

Regards,

Jagan.

• ###### Re: Sum values in function of dates

Thanks but change the modele isn't possible. Otherwise, that would not have been a problem

That why i'm looking for an advance expression with set analysis. Not sure we can.