Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
shamiul_islam
Contributor III
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,

CustomerInvoice DateSales
A6/20/202010
A6/17/202025
A6/19/202032
B5/17/202026
B5/25/202012
C4/24/20205
D5/25/202023
D5/31/202021
E1/20/202045
E1/25/202085
E2/15/202063

 

I want to calculate each customer's last invoice sales. Like,

CustomerInvoice DateSales
A6/20/202010
B5/25/202012
C4/24/20205
D5/31/202021
E2/15/202063

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 🙂

Labels (3)
7 Replies
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 :

Capture.PNG

Regards,
Taoufiq ZARRA

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

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

one solution on UI :

Dimension :

Customer

 

Measure 1:Date

Max([Invoice Date])

 

Measure 2 :Sales

FirstSortedValue(Sales,-[Invoice Date])

 

output :

Capture.PNG

 

Regards,
Taoufiq ZARRA

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

(you can mark up to 3 "solutions") 😉
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:

commQV59.PNG

shamiul_islam
Contributor III
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.

Brett_Bleess
Employee
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.
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]))