Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am making a pivot table based on the following data.
Customer Transaction Date Amount
A Sales Order 08/11/2016 123
B Sales Order 08/12/2016 234
B Sales Order 08/16/2016 546
C Sales Order 07/01/2016 100
C Sales Order 07/02/2016 200
A Invoice 08/15/2016 300
B Invoice 08/15/2016 300
dimension:
Customer
expression:
1. sum({<Transaction={'Sales Order'}, Date={'$(=max({<Transaction={'Sales Order'}>}Date))'}>}Amount)
the expected output is
A 123
B 546
C 200
the actual output is
B 546
the "max({<Transaction={'Sales Order'}>}Date)" in the above expression is not considering the customer dimension and returns the max date for all Sales Orders and so B shows up because it has max date for the sales order. To my surprise this expression is controlling the dimension.
but when i just make a column on max({<Transaction={'Sales Order'}>}Date) then i get the correct value
A 08/11/2016
B 08/16/2016
C 07/02/2016
I am sure i am missing something here.
Advise please.
Regards
sramya
Here is a sample that might help you out here.
Expression:
=FirstSortedValue({<Transaction = {'Sales Order'}>}Amount, -Date)
try like this?
=aggr(max({<Transaction={'Sales Order'}>}Amount) , Customer)
But it does not consider the max sales order date?
FirstSortedValue({$ <Transaction={'Sales Order'}>} Amount, -Date)
Try this:
FirstSortedValue({<Transaction={'Sales Order'}>} Amount, -Date)
try like
=FirstSortedValue(OrderDate,-Customer)
or
=FirstSortedValue(Customer,-OrderDate)
Your max-expression within the $-sign expansion creates an adhoc-variable which could have only one value (which will be calculated before the chart is calculated) and applied for each row. This meant you need a different approach, maybe something like this:
firstsortedvalue({<Transaction={'Sales Order'}>} Amount, -Date)
- Marcus
it does not work and returns null value
Possibly because you have multiple values against max date in real data. Try:
FirstSortedValue( Aggr(Sum({<Transaction={'Sales Order'}>} Amount), Date), -Date)
Hi Saurabh,
As Marcus explains the set returned by your set analysis expression is evaluated outside the chart, so to speak and then it is applied to the chart as a whole. So your expression becomes:
sum({<Transaction={'Sales Order'}, Date={'08/16/2016'}>}Amount)
and this is applied to all rows and so only this line is considered:
Customer Transaction Date Amount
B Sales Order 08/16/2016 546
Cheers
Andrew