Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
NEW webinar Dec. 7th: 2023 Outlook, A Pivotal Year for Data Integration SIGN ME UP!
cancel
Showing results for 
Search instead for 
Did you mean: 
jaymeknapp
Contributor II
Contributor II

Sum by Order Number with multiple dates

I have a data table that we reload every day, using the Load Date to separate on which date we grabbed the data.

Here is an example of what the data table looks like:Capture.PNG 

 

 

 

 

 

 

I'm trying to come up with a SUM(Amount) and SUM(Quantity), by distinct Transaction Number.  The challenge I'm having is that an Item can appear many times in a Transaction Number.  For example Item Label 101 in that first transaction is actually 2 line items for a Quantity of 1 each.  

Jayme

 

Labels (1)
5 Replies
agigliotti
Partner - Master III
Partner - Master III

what does your expected result is?
jaymeknapp
Contributor II
Contributor II
Author

Expected result would be as follows:

Item                Amount      Quantity

Label 101     $283              2

Label 333     $158             1

Label 458     $395             3

 

We could even include MAX(Load Date) in the set analysis if need be... just not sure how to do that. 

agigliotti
Partner - Master III
Partner - Master III

you could try using the below expression for amount expression:
=sum( aggr( FirstSortedValue( amount, -[Load Date] ), [Transaction Number] ) )
i hope it helps.
jaymeknapp
Contributor II
Contributor II
Author

this expression is giving me $0

=sum( aggr( FirstSortedValue( amount, -[Load Date] ), [Transaction Number] ) )

agigliotti
Partner - Master III
Partner - Master III

maybe this:
=sum( aggr( FirstSortedValue( Amount, -[Load Date] ), [Transaction Number] ) )