Show year in which maximum sales value occurs

[mazacini]

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

[mohammadkhatimi]

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

Kindly clarify

[mazacini]

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.

[effinty2112 · Accepted solution]

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))
A	2016	2016	1431
B	2016	2016	1661
C	2017	2017	1702

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,

[surendraj]

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:

Result:

[mazacini]

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

Regards

Joe

[mazacini]

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,

[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)