Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

taking the start month as first month (Relative)

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

3 Replies
anbu1984
Honored Contributor III

Re: taking the start month as first month (Relative)

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;

Re: taking the start month as first month (Relative)

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

Highlighted

Re: taking the start month as first month (Relative)

Hi,

one solution could be:

QlikCommunity_Thread_144240_Pic3.JPG.jpg

QlikCommunity_Thread_144240_Pic2.JPG.jpg

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