topic Sum values in function of dates in QlikView

Sum values in function of dates

Tue, 12 Aug 2014 13:39:03 GMT

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

Tue, 12 Aug 2014 20:03:28 GMT

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

ecolomer — Tue, 12 Aug 2014 21:49:52 GMT

In the file you have my proposal

Re: Sum values in function of dates

jagan — Wed, 13 Aug 2014 03:15:24 GMT

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

Wed, 13 Aug 2014 06:47:00 GMT

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.