Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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"
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.
Does it mean that set analysis does not respect chart dimensions?
not set analysis, but $() - $sign expansion
so what should be set analysis?
sum({<Transactions.TxnDate={(Max(Transactions.TxnDate))}>}Amount)
This gives 0 output.
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)
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)
Can you please let me know little bit about the usage of & operator here?
I used & for string concatenation because I needed "Date". So I concatenated
quotes and date