# New to QlikView

Discussion board where members can get started with QlikView.

Contributor III

## Show year in which maximum sales value occurs

I want to create Table B from Table A

 TABLE A Customer Year Sales A 2015 1067 B 2015 1037 C 2015 1505 A 2016 1431 B 2016 1661 C 2016 1429 A 2017 1399 B 2017 1121 C 2017 1702

 TABLE B Customer Year of Max Sale Val of Max Sale A 2016 1431 B 2016 1661 C 2017 1702

Can anyone help me?

Thanks

1 Solution

Accepted Solutions
Honored Contributor

## Re: Show year in which maximum sales value occurs

Hi Joe,

try this:

Customer FirstSortedValue(Year,-Sales) Concat(Aggr(If(Rank(Sum(Sales),1,1)=1,Year), Customer,Year),',') Max(Aggr(Sum(Sales),Year,Customer))
A201620161431
B201620161661
C201720171702

I've given a choice of two expressions to get the year of max sale. The simplest one is the FirstSortedValue expression which will work here but has some downsides. If you want to know the year based on a sum of sales where you sum over a dimension such as OrderID then FirstSortedValue won't work. Also the second expression will give you more than one year seperated by a comma if there is a tie for first place.

Regards

Andrew

20 Replies
Partner

## Re: Show year in which maximum sales value occurs

On which bases you want Max, as u shown output in 2016 u have to max of sale..

Kindly clarify

Contributor III

## Re: Show year in which maximum sales value occurs

So for Customer A

 Customer Year Sales A 2015 1067 A 2016 1431 A 2017 1399

The max sales value of 1431 occurs in Year 2016

and Customer C:

 Customer Year Sales C 2015 1505 C 2016 1429 C 2017 1702

The maximum ocurs in 2017.

Honored Contributor

## Re: Show year in which maximum sales value occurs

Hi Joe,

try this:

Customer FirstSortedValue(Year,-Sales) Concat(Aggr(If(Rank(Sum(Sales),1,1)=1,Year), Customer,Year),',') Max(Aggr(Sum(Sales),Year,Customer))
A201620161431
B201620161661
C201720171702

I've given a choice of two expressions to get the year of max sale. The simplest one is the FirstSortedValue expression which will work here but has some downsides. If you want to know the year based on a sum of sales where you sum over a dimension such as OrderID then FirstSortedValue won't work. Also the second expression will give you more than one year seperated by a comma if there is a tie for first place.

Regards

Andrew

## Re: Show year in which maximum sales value occurs

Try below

Test:

Year,

Sales

FROM

Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

Join

max(Sales) as max_Sales

Resident Test

group by Customer;

Final:

Load Customer,if(Sales=max_Sales,Year) as newYear,max_Sales as mSales Resident Test;

Drop table Test;

Find attached file for your reference.

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Valued Contributor

## Re: Show year in which maximum sales value occurs

Hi,

data:

Customer, Year, Sales

A, 2015, 1067

B, 2015, 1037

C, 2015, 1505

A, 2016, 1431

B, 2016, 1661

C, 2016, 1429

A, 2017, 1399

B, 2017, 1121

C, 2017, 1702

];

NoConcatenate

Resident

data

Order by

Customer

front end:

Result:

Contributor III

## Re: Show year in which maximum sales value occurs

Many thanks, Prashant. Could you send me script? I only have Personal Edition.

Regards

Joe

Contributor III

## Re: Show year in which maximum sales value occurs

Thanks Andrew

I suspected that the AGGR function might come into play.

But I have never used it before.

Let me test.

If you had time, could you describe how the expression works in this instance?

Regards

Joe

## Re: Show year in which maximum sales value occurs

I have posted script in my comment.

then take straight table

Dimension >

Customer

NewYear > Select Supress When value is Null

and Expression >

Sum(mSales)

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
MVP

## Re: Show year in which maximum sales value occurs

another way, to create a Flag in script. This will make your front end expression very easy and optmized

Data:

Customer, Year, Sales

A, 2015, 1067

B, 2015, 1037

C, 2015, 1505

A, 2016, 1431

B, 2016, 1661

C, 2016, 1429

A, 2017, 1399

B, 2017, 1121

C, 2017, 1702 ];

Left Join(Data)

max(Sales) as Sales,

1 as Flag

Resident Data

Group by Customer;

Create straight table

Dimension:

Customer

Year

Expression:

Sum({<Flag={1}>}Sales)