Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Thanks Andrew. Appreciate your contribution.
Excellent explanation - let me digest and see if I can understand properly.
Regards
Joe
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
Then you suggest which Year you want to show??
Regards
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.
Thanks Kushal
I confirm I only want to show latest year.
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)
Apologies, should have spotted that. Thank you
Thanks Andrew
The First Sorted Value seems to be working in this case, so I haven't explored the AGGR function yet.
Joe
Thank you for all your help