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

20 Replies
effinty2112
Master
Master

Hi Joe,

Take this one first: Aggr(Sum(Sales),Year,Customer).

Aggr statements always have one expression, Sum(Sales) in this case, and one or more dimensions , Year and Customer. Aggr works like an in-memory straight table.

Year Customer sum(Sales)
2015A1067
2015B1037
2015C1505
2016A1431
2016B1661
2016C1429
2017A1399
2017B1121
2017C1702

When the statement is executed in the chart row with customer dimension = A  QlikView thinks about this:

Year Customer sum(Sales)
2015A1067
2016A1431
2017A1399

This returns an array that is no good to us so we need to wrap it in an aggregation function to reduce it to a single value of the expression Sum(Sales). So Max( Aggr(Sum(Sales),Year,Customer)) returns the single value 1431 for Customer A.

Aggr(If(Rank(Sum(Sales),1,1)=1,Year), Customer,Year) creates in in-memory straight table that returns the Year with the highest ranked sales. This Aggr() expression would work most times without being wrapped in an aggregation function. The only time that would fail would be when there is a tie for first place. If there is a tie Aggr() would return an array and so we enclose it in a suitable aggregation function - concat.

Regards

Andrew

mazacini
Creator III
Creator III
Author

Thanks Andrew. Appreciate your contribution.

Excellent explanation - let me digest and see if I can understand properly.

Regards

Joe

mazacini
Creator III
Creator III
Author

Hi Kushal

Thanks for your repsonse.

How would that work if a customer had the same maximum value across a number of years?

Regards

Joe

PrashantSangle

Then you suggest which Year you want to show??

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

So ideally it should show both the years because that particular customer has highest sales in both the year, but if you want to show only latest year, then I need to change the code a bit. Please confirm what do you want to show.

mazacini
Creator III
Creator III
Author

Thanks Kushal

I confirm I only want to show latest year.

Kushal_Chawda

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, 1431

B, 2017, 1121

C, 2017, 1702 ];

Left Join(Data)

LOAD Customer,

     max(Sales) as Sales,

     1 as Flag

Resident Data

Group by Customer;

Left Join(Data)

LOAD Customer,

     max(Year) as Year,

     1 as isMaxSale

Resident Data

where Flag=1

Group by Customer;

DROP Field Flag;

Create straight table

Dimension:

Customer

Year

Expression:

=sum({<isMaxSale={1}>}Sales)

mazacini
Creator III
Creator III
Author

Apologies, should have spotted that. Thank you

mazacini
Creator III
Creator III
Author

Thanks Andrew

The First Sorted Value seems to be working in this case, so I haven't explored the AGGR function yet.

Joe

mazacini
Creator III
Creator III
Author

Thank you for all your help