Set analysis returns unexpected results

[agilos_mla]

Hi guys, I need your help on this interesting case. I've got this data set :

Year	Date	Amount
2009	31/12/2009	912
2010	30/06/2010	1006
2010	30/09/2010	1009
2011	31/01/2011	1101
2011	28/02/2011	1102

I would like to get into a pivot chart , the year, the max date founded for each year and the value related to this max date, whatever the selection. It will look like :

Year	Max({1}Date)	Expression
2009	31/12/2009	912
2010	30/09/2010	1009
2011	28/02/2011	1102

I encounters some issues to resolve the Expression . I tried :

sum( {1<Date={'$(=Date(Max(Date)))'}>} Amount)

it returns the right value for year 2011 but 0 for previous years. What is missing in this set analysis to obtain the desired results or another way to solve the problem without getting back bo the script?

Thx,

Michael

[#2 · Accepted solution]

Hi Michael, try this:

For the max date -> date(max(Date)

For the value associated -> firstsortedvalue(Amount,-Date)

Set analysis is not going to work in this case, since it is evaluated once per chart and not for every value within the domension.

Regards

[johnw]

Ivan's solution should work if you don't actually need a sum, though you'll probably want to add a {1} to it if I understood your question.

If you DO need the sum, maybe this?

sum({1<Date={'$(=concat({1} aggr(max({1} Date),Year),chr(39) & ',' & chr(39)))'}>} Amount)

Yes, set analysis is only evaluated once per chart, but with the expression above (if I got it right), we generate a list of all the dates of interest. Only ONE of those dates will match each line in your chart, so everything will come out as expected in the end.

[#4]

Yes John is right, in case you need the sum of amount's that will be the expression to use, although I think there is missing a date conversion for the expression to work properly, like this:

sum({1<Date={'$(=concat({1} aggr(date(max({1} Date)),Year),chr(39) & ',' & chr(39)))'}>} Amount)

Regards

[agilos_mla]

Both Solutions works great !

I'm going to use the one with the sum since I get just a sample of the data, there might be multiple value ifor the Mac Date that must be summed probably in this case.

Thx guys,

Michael