Skip to main content
Announcements
Meet the 2024 Qlik Luminaries and Partner Ambassadors! READ ON!
cancel
Showing results for 
Search instead for 
Did you mean: 
mazacini
Creator III
Creator 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
Master
Master

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
mohammadkhatimi
Partner - Specialist
Partner - Specialist

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

Kindly clarify

mazacini
Creator III
Creator III
Author

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
Master
Master

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

PrashantSangle

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.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
surendraj
Specialist
Specialist

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
Creator III
Creator III
Author

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

Regards

Joe

mazacini
Creator III
Creator III
Author

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

PrashantSangle

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.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Kushal_Chawda

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)