7 Replies Latest reply: Feb 19, 2013 4:12 PM by Robert Ebner

# Compare month to year with two different tables

Hi, I have the problem, that I would like to calculate something per month compared to an value per year. In Table 1 there are the months and in Table 2 there are the year.

Table 1:

A_Jan;                10

A_Feb;               13

A_Mär;               13

A_Apr;                15

B_Jan;                 10

B_Feb;                15

B_Mär;                14

..

Table 2:

A;                          3

B;                          2

C;                          4

..

I want to calculate in this way:

A_Jan / A;

A_Feb / A

A_Mär / A

...;

B_Jan / B;

B_Mär / B

...

How could I handle this? Any Idea? Thank you for help!

• ###### Re: Compare month to year with two different tables

SET

ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='€ #.##0,00;-€ #.##0,00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='gen;feb;mar;apr;mag;giu;lug;ago;set;ott;nov;dic';

SET DayNames='lun;mar;mer;gio;ven;sab;dom';

Table_1:

[

month,valMonth

A_Jan,                10

A_Feb,               13

A_Mar,               13

A_Apr,                15

B_Jan,                 10

B_Feb,                15

B_Mar,                14

]
;

Table_2:

[

year,valYear

A,                          3

B,                          2

C,                         4

]
;

T1:

NoConcatenate

*,

left(month,1) as year

Resident Table_1;

Left Join

Table_2:

[

year,valYear

A,                          3

B,                          2

C,                         4

]
;

Hope it helps

• ###### Re: Compare month to year with two different tables

Thank you for your fast answer, but I think you got me wrong. Think, I explained bad - sorry.

My problem is, that I don't know how to divide the diffrenet values as there is a different number of lines in the tables:

eg.

Table 1            Table 2   = Result

A_Jan (10)     :   A (3) = 3,33

A_Feb  (13)    :    A (3) = 4,33

A_... (xy)        :     A (y) = x

B_Jan            :      B =

B_Feb            :      B =

and so on.. I have a lot of different types.

Hope it is esasier now!?

• ###### Re: Compare month to year with two different tables

Well, but I think that you are a step before the solution because you have now a table where in each row you have values

year     month     valyear     valmonth

so you just have to compute valmonth / valyear for each row and then sum or aggregate or count or ... the result ... am I wrong?

Hope it helps

• ###### Re: Compare month to year with two different tables

Ok, now I understand ...

How will I get the result in the second step? How can I tell QV to compare the right rows.