# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
cancel
Showing results for
Did you mean:
Contributor III

## Calculate Sum of Last Invoice Sales with Set Analysis

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.

Labels (3)

• ### Sales

7 Replies

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 :

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

one solution on UI :

Dimension :

Customer

Measure 1:Date

``Max([Invoice Date])``

Measure 2 :Sales

``FirstSortedValue(Sales,-[Invoice Date])``

output :

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

One solution is.

``````tab1:
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:
Resident tab1
Order By Customer, [Invoice Date] Desc;

Drop Table tab1;``````

Output:

Contributor III
Author

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

Employee

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
MVP

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