topic Re: Top 2 Company Over 2 Years in QlikView

Top 3 Company Over 2 Years

Thu, 05 Jun 2014 12:45:31 GMT

Hello Friends,

I have following table,

Company	Sales	Year
A	205	2006
A	301	2005
A	56	2004
B	257	2006
B	654	2005
B	147	2004
C	256	2006
C	65	2005
C	59	2004
D	598	2006
D	254	2005
D	10	2004

and I have to find top 2 company over past 2 years, I want an output table as

Comapany	Sales
B	911
D	852
A	506

please if anyone has any idea,help me out

Re: Top 2 Company Over 2 Years

Thu, 05 Jun 2014 12:47:29 GMT

create a table with company as a dimension, and then use an expression that outputs the sum of sales over the 2 year period

Then choose to display only the top three results

Re: Top 2 Company Over 2 Years

Thu, 05 Jun 2014 12:47:34 GMT

try this

=FirstSortedValue(Company,

(-aggr(sum(Sales), Company))

)

bets regards

Chris

Re: Top 3 Company Over 2 Years

Thu, 05 Jun 2014 13:02:39 GMT

How would I Sum of sales over 2 Year period? Thats exactly I want,please share the expression

Re: Top 3 Company Over 2 Years

tresB — Thu, 05 Jun 2014 13:10:16 GMT

=Sum({<Year={">$(=Max(Year)-2) <=$(=Max(Year))"}>}Sales)

Re: Top 3 Company Over 2 Years

Thu, 05 Jun 2014 13:12:00 GMT

=sum({$<Year={vCurrentYear},Year={vLastYear}>}Sales)

Where the variables are for the two years that you want to sum over

Re: Top 3 Company Over 2 Years

its_anandrjs — Thu, 05 Jun 2014 13:16:05 GMT

Take the straight table and

Dimension:- Company

Expression:- if( Rank(Sum({<Year = {'>=$(=Max(Year-1)) <=$(=Max(Year))'} >} Sales)) <= 3, Sum({<Year = {'>=$(=Max(Year-1)) <=$(=Max(Year))'} >} Sales))

This will give as out put that you want see snap

If you want to get only for last 2 years data then use

Exper:- Sum({<Year = {'>=$(=Max(Year-1)) <=$(=Max(Year))'} >} Sales))

Re: Re: Top 3 Company Over 2 Years

Thu, 05 Jun 2014 13:16:18 GMT

Like this

Re: Re: Re: Top 3 Company Over 2 Years

Thu, 05 Jun 2014 13:17:31 GMT

Sorry, this one shows the top 3 values

Re: Top 3 Company Over 2 Years

Thu, 05 Jun 2014 13:24:58 GMT

Thank you Robert

Re: Top 3 Company Over 2 Years

eduardo_sommer — Thu, 05 Jun 2014 13:25:32 GMT

I created a qvw with a straight table, with Company as the Dimension and the expression

=sum({$<Year={'$(=Max(Year))','$(=Max(Year)-1)'}>} Sales)

On the Dimension Limits, I set to show only te 2 largest values, with no Other.

See the attached qvw.

Eduardo

Re: Top 3 Company Over 2 Years

its_anandrjs — Thu, 05 Jun 2014 13:33:43 GMT

Hi Bhawna,

Did you check the result of the expression =sum({$<Year={vCurrentYear},Year={vLastYear}>}Sales)

you get only sum of last 2 years but not B,D,A as you discussed in the post for top3 for last 2 years did you try with the this expression

=if( Rank(Sum({<Year = {'>=$(=Max(Year-1)) <=$(=Max(Year))'} >} Sales)) <= 3, Sum({<Year = {'>=$(=Max(Year-1)) <=$(=Max(Year))'} >} Sales))

Which will give for last 2 years sales and for top 3 customers with ranking use.

Over all Robert expression is correct but not ranking the expression

Regards

Re: Top 3 Company Over 2 Years

Thu, 05 Jun 2014 14:06:53 GMT

Thank you Friends for helping me by giving your ideas.. thank you so much all

Re: Top 3 Company Over 2 Years

ravindraa — Thu, 31 Jul 2014 06:57:35 GMT

Hi Robert Daitzman,

I have also same requierment i am using your logic but i have used variables instead of direct year's like

vcurrentyear=year(this is my field)

vpreviousyear = max(year)-1

i am trying to write set analysis like below but i am not getting any value it is showing emty table if you know any suggession please give to me.

i have used below two expression :

=sum({$<Year2={vcurrentyear},Year2={vpreviousyear}>}Sales2) .

=sum({<Year2={"$(=vCurrentYear)"},Year2={"$(=vpreviousyear)"}>}sales2)

Re: Top 3 Company Over 2 Years

Thu, 31 Jul 2014 12:21:38 GMT

Ravindraa,

Which of those two expressions uses the correct field names? Those two have different capitalizations which will be pointing towards differing fields