taking the start month as first month (Relative)

[#1]

Hi,

I am trying to implement a logic, wherein a chart displays months on the x-axis. (integer values 1 to 20)

but as the start month is different for different items, so i want the months to be relative.. as in the starting month of an item should be 1 and so on.. till 20 months.. so if starting month for 'A' is 'may 2010' and starting month for 'B' is 'Jan 2000', both should correspond to '1'. is there a way to achieve this?

instead of going only till 20, it could continue infinitely (till wherever the data s present) as well

[anbu1984]

Initial:

Load Item,Date#(YearMth,'YYYYMM') As YearMth,Sales Inline [

Item,YearMth,Sales

A,201401,100

A,201405,200

A,201305,100

B,201101,100

B,201201,100

B,201202,300 ];

NoConcatenate

Final:

Load *, AutoNumber(RecNo(),Item) As MthNo Resident Initial Order by Item,YearMth;

Drop Table Initial;

[its_anandrjs]

Hi,

Try this way and use AutoNumber for each year and month for auto numbering.

LOAD

RNo,

Year(Date#(Year,'YYYY')) as Year,

Month(Date#(Month,'MM')) as Month,

MonthName(MakeDate(Year(Date#(Year,'YYYY')),Month(Date#(Month,'MM')))) as MonthNames,

AutoNumber(RNo, Year) as AutoNum;

LOAD * INLINE [

    RNo, Year, Month

    1, 2011, 1

    2, 2011, 2

    3, 2010, 1

    4, 2010, 2

    5, 2010, 3

    6, 2010, 4

    7, 2012, 1

    8, 2012, 2

    9, 2014, 1

];

Regards

Anand

[MarcoWedel]

Hi,

one solution could be:

tabItems:
LOAD item,
    Date#(month,'MMM YYYY') as month,
    Ceil(Rand()*100) as [random data]
Inline [
item, month
A, Jun 2010
A, Aug 2010
A, Jul 2010
A, Sep 2010
A, May 2010
A, Oct 2010
B, Mar 2000
B, Jan 2000
B, Feb 2000
B, May 2000
B, Apr 2000
C, Apr 2005
C, Aug 2005
C, May 2005
C, Jul 2005
C, Jun 2005
];

Left Join (tabItems)
LOAD Distinct
    item,
    month,
    AutoNumber(month, item) as monthNum
Resident tabItems
Order By month;

hope this helps

regards

Marco