month difference

[#1]

hi,

I have a table in which I have 2 columns like these :

01/09/2002	200209
01/10/2002	200209
01/12/2002	200209
01/01/2003	200209
01/02/2003	200209
01/03/2003	200209
01/04/2003	200209
01/08/2003	200209
01/09/2003	200209
01/11/2003	200209
01/12/2003	200209
01/01/2004	200209
01/03/2004	200209
01/05/2004	200209
01/06/2004	200209
01/08/2004	200209
01/09/2002	200209
01/12/2002	200209

Now I want to show the difference (in months) between the two, how can I achieve this?

[sushil353]

Hi,

Try This:

Month(Column1)-Month(Date(Date#(Column2,YYYYMM)))

HTH

Sushil

[jonathandienst]

Hi

You need to take years into account with something like:

=(Year(Column1) * 12 + Month(Column1)) - (Left(Column2, 4)  * 12 + Right(Column2, 2))

HTH

Jonathan

[its_anandrjs]

Hi,

Try this ways

LOAD

Date(Date#(Date1,'DD/MM/YYYY'),'MM/YYYY') as NewDate1,

Date(Date#(Date2,'YYYYMM'),'MM/YYYY') as NewDate2,

Date(Date(Date#(Date1,'DD/MM/YYYY'),'MM/YYYY') - Date(Date#(Date2,'YYYYMM')),'MM') as NewDiff;

LOAD * Inline  [

Date1,Date2

01/09/2002,    200209

01/10/2002,    200209

01/12/2002,    200209

01/01/2003,    200209

01/02/2003,    200209

01/03/2003,    200209

01/04/2003,    200209

01/08/2003,    200209

01/09/2003,    200209

01/11/2003,    200209

01/12/2003,    200209

01/01/2004,    200209

01/03/2004,    200209

01/05/2004,    200209

01/06/2004,    200209

01/08/2004,    200209

01/09/2002,    200209

01/12/2002,    200209  ];

Regards

Anand

[#5]

hi Jonathan,

I had used this expression in the script, it errored out.

If I display existing columns as dimensions in a pivot table, I can't see them.

Is there a way to add a calculated dimension in a table box?

[MarcoWedel]

Hi,

one solution could be:

LOAD *,
    Month(date2)-Month(date1)+(Year(date2)-Year(date1))*12 as MonthDiff;
LOAD MonthName(Date#(date2,'DD/MM/YYYY')) as date2,
    MonthName(Date#(date1,'YYYYMM')) as date1
INLINE [
    date2, date1
    01/09/2002, 200209
    01/10/2002, 200209
    01/12/2002, 200209
    01/01/2003, 200209
    01/02/2003, 200209
    01/03/2003, 200209
    01/04/2003, 200209
    01/08/2003, 200209
    01/09/2003, 200209
    01/11/2003, 200209
    01/12/2003, 200209
    01/01/2004, 200209
    01/03/2004, 200209
    01/05/2004, 200209
    01/06/2004, 200209
    01/08/2004, 200209
    01/09/2002, 200209
    01/12/2002, 200209
];

hope this helps

regards

Marco

[its_anandrjs]

Hi,

There is another way for the doing the same problem is by

LOAD *,
Year(NewDate1) - Year(NewDate2) as YearDiff,
Month(NewDate1) - Month(NewDate2) as MonthDiff,
(Year(NewDate1) - Year(NewDate2)) * 12 +
Month(NewDate1) - Month(NewDate2) as TotalMonDiff;
 
Load *,
Date(Date#(Date1,'DD/MM/YYYY'),'MM/YYYY') as NewDate1,
Date(Date#(Date2,'YYYYMM'),'MM/YYYY') as NewDate2;

LOAD * INLINE [
Date1, Date2
01/09/2002, 200209
01/10/2002, 200209
01/12/2002, 200209
01/01/2003, 200209
01/02/2003, 200209
01/03/2003, 200209
01/04/2003, 200209
01/08/2003, 200209
01/09/2003, 200209
01/11/2003, 200209
01/12/2003, 200209
01/01/2004, 200209
01/03/2004, 200209
01/05/2004, 200209
01/06/2004, 200209
01/08/2004, 200209
01/09/2002, 200209
01/12/2002, 200209
];

And you get table as

Regards

Anand