Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I want to calculate last invoice sales of each customer. Like there is a data set,
Customer | Invoice Date | Sales |
A | 6/20/2020 | 10 |
A | 6/17/2020 | 25 |
A | 6/19/2020 | 32 |
B | 5/17/2020 | 26 |
B | 5/25/2020 | 12 |
C | 4/24/2020 | 5 |
D | 5/25/2020 | 23 |
D | 5/31/2020 | 21 |
E | 1/20/2020 | 45 |
E | 1/25/2020 | 85 |
E | 2/15/2020 | 63 |
I want to calculate each customer's last invoice sales. Like,
Customer | Invoice Date | Sales |
A | 6/20/2020 | 10 |
B | 5/25/2020 | 12 |
C | 4/24/2020 | 5 |
D | 5/31/2020 | 21 |
E | 2/15/2020 | 63 |
I used this expression: Sales = sum({<InvDate={'$(Max_InvDate)'}>} Gross_Value)
where Max_InvDate=max(date(INVDATE)) and InvDate=Date(INVDATE,'M/D/YYYY')
and in the dimension I used Customer Name and MaxInvDate=aggr(max(Date(INVDATE)),CUSTShortName).
But I don't get the actual sale like above table.
What's wrong with this expression? Anyone please help me with expression.
Thanks in Advance 🙂
one solution on load Script :
Data:
LOAD Customer, Date([Invoice Date]) as [Invoice Date], Sales INLINE [
Customer, Invoice Date, Sales
A, 6/20/2020, 10
A, 6/17/2020, 25
A, 6/19/2020, 32
B, 5/17/2020, 26
B, 5/25/2020, 12
C, 4/24/2020, 5
D, 5/25/2020, 23
D, 5/31/2020, 21
E, 1/20/2020, 45
E, 1/25/2020, 85
E, 2/15/2020, 63
];
output:
noconcatenate
load Customer, Max([Invoice Date]) as [Invoice Date], FirstSortedValue(Sales,-[Invoice Date]) as Sales resident Data group by Customer;
drop table Data;
output :
one solution on UI :
Dimension :
Customer
Measure 1:Date
Max([Invoice Date])
Measure 2 :Sales
FirstSortedValue(Sales,-[Invoice Date])
output :
One solution is.
tab1:
LOAD * INLINE [
Customer, Invoice Date, Sales
A, 6/20/2020, 10
A, 6/17/2020, 25
A, 6/19/2020, 32
B, 5/17/2020, 26
B, 5/25/2020, 12
C, 4/24/2020, 5
D, 5/25/2020, 23
D, 5/31/2020, 21
E, 1/20/2020, 45
E, 1/25/2020, 85
E, 2/15/2020, 63
];
tab2:
LOAD * Where Sort=1;
LOAD *, AutoNumber(RecNo(),Customer) As Sort
Resident tab1
Order By Customer, [Invoice Date] Desc;
Drop Table tab1;
Output:
This is not working. This expression shows only some customers last invoice sales. But I need all customer's last invoice sales.
I would suspect the correct thing to do in this case is attach a sample application that has a full data model etc. is the right thing to do in order to get a correct answer, without that, folks will just be guessing and likely getting it wrong again, so if you are still working upon things, please attach sample file, that should help folks better understand things and hopefully get you the right suggestion.
The only other thing would be the following Design Blog post may job something too:
https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822
Regards,
Brett
May be you have multiple sales for the same customer on the max date? If you do, can you try this
FirstSortedValue(Aggr(Sum(Sales), Customer, [Invoice Date]), -Aggr([Invoice Date], Customer, [Invoice Date]))