Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
bibopipo
Creator II
Creator II

how to match data

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

6 Replies
Not applicable

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.

bibopipo
Creator II
Creator II
Author

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

Could you help me please ?

Not applicable

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.

Not applicable

Hi

use the pivote table it exactly this type of data

bibopipo
Creator II
Creator II
Author

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.

Not applicable

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.