Association is missing

[Anonymous]

Hi,

I have yearmonth (combination of year n month), sales, salesperson data.

I have taken year and months from yearmonth filed.

I have created two listboxes as year and month a bar chart with sum of sales.

how ever if i wish to select year and month at a time the selection is not getting possible in the list boxes.

Ex: if i want to select 2012 Jan the selection is missing and association is not taking place,

Please let me know by seeing the attachment

[Anonymous]

any help please

[Anonymous]

Hi

Try this:

Use preceding load:

Association:

load YearMonth,

    Right(YearMonth,4) as Year,

    left(YearMonth,3) as Month,

    Sales,

    SalesPerson;

LOAD * INLINE [

    YearMonth, Sales, SalesPerson

    jan2012, 100, John

    feb2012, 250, James

    mar2012, 300, Tim

    jan2013, 400, Tom

    feb2013, 500, Hatson

    mar2013, 600, Adsr

];

Then change your list boxes to use Year & Month fields respectively and Edit your group on the bar chart to use these fields as well...

Sample attached...

Hope this helps you!

[tamilarasu]

Hi John,

Have a look at the attachment.

Association:
Load *, Date(Date#(Left(YearMonth,3),'MMM'),'MMM') as Month, Date#(Right(YearMonth,4),'YYYY') as Year;
LOAD * INLINE [
YearMonth, Sales, SalesPerson
jan2012, 100, John
feb2012, 250, James
mar2012, 300, Tim
jan2013, 400, Tom
feb2013, 500, Hatson
mar2013, 600, Adsr
];

[its_anandrjs]

Create this Year and Month in your load script will be better.

Association:

Load

*,

year(Date#(YearMonth,'MMMYYYY')) as Year,

Month(Date#(YearMonth,'MMMYYYY')) as Month;

LOAD * INLINE [

    YearMonth, Sales, SalesPerson

    jan2012, 100, John

    feb2012, 250, James

    mar2012, 300, Tim

    jan2013, 400, Tom

    feb2013, 500, Hatson

    mar2013, 600, Adsr

];

See the attached file.

Regards

Anand

[Anonymous]

Thanks all for your responses

I dont want to split the year and month fields at load script level,

I want to split them in application side only.

[jonathandienst]

Best practice is to convert date fields (like your yearmonth) into proper numerical dates and if you are analysing by year and month, to create year and month fields. Using calculated fields in list boxes effectively makes selections on the underlying fields and this may not be quite what you want.

Load like this

Association:

LOAD *,

  Year(YearMonth) as Year,

  Month(YearMonth) as Month

;

LOAD Date#(YearMonth, 'MMMyyyy') as YearMonth,

  Sales,

  SalesPerson

;

LOAD * INLINE [

    YearMonth, Sales, SalesPerson

    jan2012, 100, John

    feb2012, 250, James

    mar2012, 300, Tim

    jan2013, 400, Tom

    feb2013, 500, Hatson

    mar2013, 600, Adsr

];