6 Replies Latest reply: Nov 20, 2012 6:32 AM by Syed Khaled Shahbaaz RSS

    how to match data

    steve peroni

      Dear all,

      I've the following tabel

       

      Customer     Year     Contract         Salesman    Salesman% per contract

      AAA             2012     123-1                 01                         6%

      AAA             2011     123-1                 01                         5%

      AAA             2012     456                    01                         4%

      AAA             2011     456                    01                         3%

      ....

       

      What I would have is a structure like this:

                                                       

      Customer     Contract     Salesman      2012     2011  

      AAA               123-1           01               6%       5%

      AAA               456              01               4          3%

      ....

      ...

         

               

      I would have the current year and previous year into my prospect: choosing 2012, I can analyze also 2011.

       

      Can I solve this problem ?

      Help please

      Thanks in advance

        • Re: how to match data

          Hi,

           

          Not sure if I understand this correctly, but if you are trying to transpose the Year column and split each year into one column like 2012,2011 and so on, you could do that use a Pivot Table on the front end or using a crosstable load at the back end.

           

          If you are just trying to compare two years, say for example, if selecting 2012 should give sales of both 2012 and 2011 and selecting 2011 should give both 2011 and 2010, you could use a year vs previous year comparison here.

           

          Is that what you are looking for?

           

          -Regards,

           

          -Khaled.

            • Re: how to match data
              steve peroni

              Yes, I'm trying to build the structure above (see my example), but without success.

              Could you help me please ?

                • Re: how to match data

                  Hi,

                   

                   

                  In a Pivot Table, add Customer, Contract, Year and Salesman as dimensions

                  Add the expression for Salesman% per contract.

                   

                  You could just add click-drag and arrange Year horizontally at the top in a pivot table. This should give you the desired result.

                   

                  Please see the attached file.

                   

                  Hope that helps.

                   

                  Regards,

                   

                  -Khaled.

                    • Re: how to match data
                      steve peroni

                      The problem is that when I choose 2012 on my listbox, I see only 2012 and nothing on previous year. Choosing 2011, I see all the values, but nothing for 2010.

                      Do I've to use set analisys ? I'm a bit confused.

                        • Re: how to match data

                          Hi,

                           

                           

                          Just try adding the following expressions in your chart/table:

                           

                          sum({<Year={$(vLastYear)}>}Salesman%_Per_Contract)

                           

                          =sum({<Year={$(vCurrentYear)}>}Salesman%_Per_Contract) 

                           

                           

                          *****************For Selected Year vs Previous Year

                           

                           

                          Define two variables in Variable Overview (use Ctrl+Alt+V keys) as:

                           

                          vCurrentYear = GetCurrentField(Year)

                           

                          vLastYear =GetCurrentField(Year)-1

                           

                           

                          *****************For Current Year vs Previous Year4

                           

                          Define two variables in Variable Overview (use Ctrl+Alt+V keys) as:

                           

                          vCurrentYear = Year(Today())

                           

                          vLastYear =Year(Today())-1

                           

                          Hope that helps.

                           

                          Regards,

                           

                          -Khaled.

                  • Re: how to match data

                    Hi

                     

                    use the pivote table it exactly this type of data