Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

4 Replies
Not applicable
Author

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

ecolomer
Master II
Master II

In the file you have my proposal

Date_2File.png

jagan
Partner - Champion III
Partner - Champion III

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.

Not applicable
Author

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.