Announcements
cancel
Showing results for
Did you mean:
Creator 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

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

Creator III
Author

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

Regards

Joe

Creator III
Author

Hi Kushal

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

Regards

Joe

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 🙂

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.

Creator III
Author

Thanks Kushal

I confirm I only want to show latest year.

Data:

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)

max(Sales) as Sales,

1 as Flag

Resident Data

Group by Customer;

Left Join(Data)

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)

Creator III
Author

Apologies, should have spotted that. Thank you

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

Creator III
Author

Thank you for all your help

Community Browser