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

[Anonymous]

try like this?

=aggr(max({<Transaction={'Sales Order'}>}Amount) , Customer)

[#3]

But it does not consider the max sales order date?

[maxgro]

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

[tresesco]

Try this:

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

[Chanty4u]

try like

=FirstSortedValue(OrderDate,-Customer)

or

=FirstSortedValue(Customer,-OrderDate)

[marcus_sommer]

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

[#8]

it does not work and returns null value

[tresesco]

Possibly because you have multiple values against max date in real data. Try:

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

[effinty2112]

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