how to match data

[Anonymous]

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

[#2]

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.

[Anonymous]

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

Could you help me please ?

[#4]

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.

[#5]

Hi

use the pivote table it exactly this type of data

[Anonymous]

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.

[#7]

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.