Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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
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.
this expression is giving me $0
=sum( aggr( FirstSortedValue( amount, -[Load Date] ), [Transaction Number] ) )