topic Re: how to match data in QlikView

how to match data

Anonymous — Tue, 20 Nov 2012 10:16:20 GMT

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

Tue, 20 Nov 2012 10:22:57 GMT

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

Anonymous — Tue, 20 Nov 2012 10:30:05 GMT

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

Tue, 20 Nov 2012 10:40:33 GMT

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

Tue, 20 Nov 2012 10:45:31 GMT

use the pivote table it exactly this type of data

Re: how to match data

Anonymous — Tue, 20 Nov 2012 11:01:54 GMT

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

Tue, 20 Nov 2012 11:32:20 GMT

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.