topic Re: Show year in which maximum sales value occurs in QlikView

Show year in which maximum sales value occurs

mazacini — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Show year in which maximum sales value occurs

mohammadkhatimi — Thu, 28 Sep 2017 09:56:39 GMT

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

Kindly clarify

Re: Show year in which maximum sales value occurs

mazacini — Thu, 28 Sep 2017 10:01:29 GMT

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.

Re: Show year in which maximum sales value occurs

effinty2112 — Thu, 28 Sep 2017 10:10:19 GMT

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

Re: Show year in which maximum sales value occurs

PrashantSangle — Thu, 28 Sep 2017 10:10:35 GMT

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,

Re: Show year in which maximum sales value occurs

surendraj — Thu, 28 Sep 2017 10:15:15 GMT

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:

Re: Show year in which maximum sales value occurs

mazacini — Thu, 28 Sep 2017 10:15:51 GMT

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

Regards

Joe

Re: Show year in which maximum sales value occurs

mazacini — Thu, 28 Sep 2017 10:19:00 GMT

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

PrashantSangle — Thu, 28 Sep 2017 10:22:53 GMT

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,

Re: Show year in which maximum sales value occurs

Kushal_Chawda — Thu, 28 Sep 2017 10:32:49 GMT

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)

Re: Show year in which maximum sales value occurs

effinty2112 — Thu, 28 Sep 2017 10:36:34 GMT

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)
2015	A	1067
2015	B	1037
2015	C	1505
2016	A	1431
2016	B	1661
2016	C	1429
2017	A	1399
2017	B	1121
2017	C	1702

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

Year	Customer	sum(Sales)
2015	A	1067
2016	A	1431
2017	A	1399

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

Re: Show year in which maximum sales value occurs

mazacini — Thu, 28 Sep 2017 11:18:35 GMT

Thanks Andrew. Appreciate your contribution.

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

Regards

Joe

Re: Show year in which maximum sales value occurs

mazacini — Thu, 28 Sep 2017 11:21:28 GMT

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

Re: Show year in which maximum sales value occurs

PrashantSangle — Thu, 28 Sep 2017 11:23:33 GMT

Then you suggest which Year you want to show??

Regards

Re: Show year in which maximum sales value occurs

Kushal_Chawda — Thu, 28 Sep 2017 11:47:40 GMT

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.

Re: Show year in which maximum sales value occurs

mazacini — Thu, 28 Sep 2017 11:52:24 GMT

Thanks Kushal

I confirm I only want to show latest year.

Re: Show year in which maximum sales value occurs

Kushal_Chawda — Thu, 28 Sep 2017 11:57:50 GMT

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)

Re: Show year in which maximum sales value occurs

mazacini — Thu, 28 Sep 2017 21:55:42 GMT

Apologies, should have spotted that. Thank you

Re: Show year in which maximum sales value occurs

mazacini — Thu, 28 Sep 2017 21:57:06 GMT

Thanks Andrew

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

Joe

Re: Show year in which maximum sales value occurs

mazacini — Thu, 28 Sep 2017 21:58:12 GMT

Thank you for all your help