Calculate Sum of Last Invoice Sales with Set Analysis

[shamiul_islam]

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 🙂

[Taoufiq_Zarra]

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 :

[Taoufiq_Zarra]

one solution on UI :

Dimension :

Customer

Measure 1:Date

Max([Invoice Date])

Measure 2 :Sales

FirstSortedValue(Sales,-[Invoice Date])

output :

[Saravanan_Desingh]

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;

[Saravanan_Desingh]

Output:

[shamiul_islam]

This is not working. This expression shows only some customers last invoice sales. But I need all customer's last invoice sales.

[Brett_Bleess]

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

[sunny_talwar]

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