Discussion Board for collaboration related to QlikView App Development.
I want to create Table B from Table A
|Customer||Year of Max Sale||Val of Max Sale|
Can anyone help me?
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.
When the statement is executed in the chart row with customer dimension = A QlikView thinks about this:
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.
Thanks Andrew. Appreciate your contribution.
Excellent explanation - let me digest and see if I can understand properly.
Thanks for your repsonse.
How would that work if a customer had the same maximum value across a number of years?
Then you suggest which Year you want to show??
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.
I confirm I only want to show latest year.
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 ];
max(Sales) as Sales,
1 as Flag
Group by Customer;
max(Year) as Year,
1 as isMaxSale
Group by Customer;
DROP Field Flag;
Create straight table
Apologies, should have spotted that. Thank you
The First Sorted Value seems to be working in this case, so I haven't explored the AGGR function yet.
Thank you for all your help