6 Replies Latest reply: Nov 16, 2016 3:10 AM by Jonathan Dienst RSS

    Association is missing

    John Smith

      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

        • Re: Association is missing
          John Smith

          any help please

            • Re: Association is missing
              Anand Chouhan

              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

            • Re: Association is missing
              Deran Naidoo

              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!

              • Re: Association is missing
                Tamil Nagaraj

                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
                ]
                ;

                • Re: Association is missing
                  John Smith

                  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.

                  • Re: Association is missing
                    Jonathan Dienst

                    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

                    ];