Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

mazacini
Contributor III

Show year in which maximum sales value occurs

I want to create Table B from Table A

 

TABLE A
CustomerYearSales
A20151067
B20151037
C20151505
A20161431
B20161661
C20161429
A20171399
B20171121
C20171702

   

TABLE B
CustomerYear of Max SaleVal of Max Sale
A20161431
B

2016

1661
C20171702

Can anyone help me?

Thanks

1 Solution

Accepted Solutions
effinty2112
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

View solution in original post

20 Replies
Partner
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

mazacini
Contributor III

Re: Show year in which maximum sales value occurs

So for Customer A

 

CustomerYearSales
A20151067
A20161431
A2017

1399

The max sales value of 1431 occurs in Year 2016

and Customer C:

 

CustomerYearSales
C20151505
C20161429
C2017

1702

The maximum ocurs in 2017.

effinty2112
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

View solution in original post

Re: Show year in which maximum sales value occurs

Try below

Test:

LOAD Customer,

    Year,

    Sales

FROM

Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

Join

Load Customer,

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.
surendraj
Valued Contributor

Re: Show year in which maximum sales value occurs

Hi,

data:

LOAD * INLINE [

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

load  *

Resident

data

Order by

Customer

front end:

Capture.PNG

Result:

Capture.PNG

mazacini
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

mazacini
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.

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:

LOAD * Inline [

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)

LOAD Customer,

     max(Sales) as Sales,

     1 as Flag

Resident Data

Group by Customer;

Create straight table

Dimension:

Customer

Year

Expression:

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