max does not work inside set analysis

[#1]

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

[#2]

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 ?

[jonathandienst]

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)

[tresesco]

This?

FirstSortedValue({<Transaction={'Sales Order'}>} Aggr(Sum({<Transaction={'Sales Order'}>} Amount), Date), -Date)

[sunny_talwar · Accepted solution]

Here is a sample that might help you out here.

Expression:

=FirstSortedValue({<Transaction = {'Sales Order'}>}Amount, -Date)

[#6]

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?

[sunny_talwar]

sramya1234‌ I am not sure I understand your question, would you be able to clarify what exactly do you not understand about this expression

[#8]

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?