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
in all these expressions, we are not considering one fact that i need to consider date of only sales orders and not of the invoices.
above expressions perhaps will give amount only if there is a sales order on the first sorted value of date which may not be always true.
i may have last sales order on 08/11 but the first sorted value of date may 09/11.
Does it make sense to you ?
That's why the last parameter of the FirstSortedValue is negative ( -Date), so you are evaluating "LastSortedValue"
And Marcus' expression filters for seales orders:
=firstsortedvalue({<Transaction={'Sales Order'}>} Amount, -Date)
This?
FirstSortedValue({<Transaction={'Sales Order'}>} Aggr(Sum({<Transaction={'Sales Order'}>} Amount), Date), -Date)
Here is a sample that might help you out here.
Expression:
=FirstSortedValue({<Transaction = {'Sales Order'}>}Amount, -Date)
so the set analysis applies to the sorted weight that Date here and not to the expression which is amount? Can you please why does it work?
sramya1234 I am not sure I understand your question, would you be able to clarify what exactly do you not understand about this expression
Do the if you just write firstSortedValue(Amount, -Date) then it will sort all the dates and get the amount of the greatest date.
but when you do the expression firstSortedValue({<Transaction={''Sales Order'}>}Amount, -Date) then it takes amount of the greatest date of all sales orders…right?
So my question is...
once you have instead set analysis if the firstSortedValue then does it...
1. Sort only SalesOrder's date
or
2. Sorts all dates but take the amount from the sale order..
How does it work?