Set analysis question about max function

[#1]

HI,

I got one pivot table which looks like this

The last expression is defined as sum({<Transactions.TxnDate={$(=Max(Transactions.TxnDate))}>}Amount)

Here is the question...

When i use max(Transactions.TxnDate) alone as a expression then it shows 1/1/2014 an fit is correct because i have one transaction on that day.

But when it is used in a set analysis then it gives me the last day of 1/31/2014....I expect it to return the same result whether it is used in set analysis or alone.

I also have a master calendar in this document so 1/31/2014 does exist in data model but there is no transaction on that day.

Can you please help understand this behavior?

Regards,

Saurabh

[SergeyMak]

Hi,

you can see in your table the second line with Max(Transactions.TxnDate)="1/31/2014"

When you use $-sign expansion you get the maximum for whole data set, which is in your app "1/31/2014"

[Anonymous]

In the column before last, there is Transaction.TxnDate 1/31/2014.  Set analysis evaluates the whole data set, in this case selected data, not per table row, hence you get this result.

[#4]

Does it  mean that set analysis does not respect chart dimensions?

[SergeyMak]

not set analysis, but $() - $sign expansion

[#6]

so what should be set analysis?

sum({<Transactions.TxnDate={(Max(Transactions.TxnDate))}>}Amount)

This gives 0 output.

[SergeyMak]

If you are trying to get the value of last day when you had sales

try this expression

Sum({$<Transactions.TxnDate={$(='"' & Date(MAX({$<Transactions.TxnDate=p({$<Amount={">0"}>})>}Transactions.TxnDate)) & '"')}>}Amount)

[SergeyMak]

And another idea

You can have additional date-field (Transactions.TxnDateFltr) in your Transactions table which is equal to Transactions.TxnDate, but is not connected to calendar table and then you can use it like

sum({<Transactions.TxnDate={$(=Max(Transactions.TxnDateFltr))}>}Amount)

[#9]

Can you please let me know little bit about the usage of & operator here?

[SergeyMak]

I used & for string  concatenation because I needed "Date". So I concatenated

quotes and date