Why won't this work...

[samvile18]

...can anyone tell me why this won't work?

=sum({<Measure={'FUM'},FExtractDate={'$(=MonthEnd(MaxDate))'}>}Value)

MaxDate is a date field along with FExtractDate - I need to sum the FUM value where these two match.

[Miguel_Angel_Baeyens]

Hi,

Do both MonthEnd() and FExtractDate and MaxDate have the same exact format (i.e.: DD/MM/YYYY)? You can try with:

Sum({< Measure = {'FUM'}, FExtractDate = {'$(=Date(MonthEnd(MaxDate)))'} >} Value)

Hope that helps.

Miguel

[fdelacal]

=sum({<Measure={'FUM'},FExtractDate={'$(=MonthEnd(MaxDate))'}>}Value)

try with " "

{"FUM"}  {"$(=MonthEnd(MaxDate))"}

Hope it helps you

[Miguel_Angel_Baeyens]

Hi,

Single quoting in set analysis is OK as long as the value is a fixed, literal value. FUM is and so a date in a format DD/MM/YYYY. Although that will work, double quoting in set analysis is used with search values and accept wildcards, for example, to return a set where date ends with "2012":

Sum({< FExtractDate = {"*2012"} >} Value)

Hope that helps.

Miguel

[samvile18]

I can't get either of those answers to work...this really is puzzling me.

I have no idea why this is returning nothing.

[Miguel_Angel_Baeyens]

Hi,

Is MaxDate a field, a variable? Does it have the same format as FExtractValue?

Miguel

[samvile18]

MaxDate is a field I've created within the script

[samvile18]

It should have the same formatting as I've used the Date expression within the script for both.

[Miguel_Angel_Baeyens]

Hi,

Try then with Max() function:

Sum({< Measure = {'FUM'}, FExtractDate = {'$(=MonthEnd(Max(MaxDate)))'} >} Value)

Note that set analysis works once for the whole chart, and not line by line. In this case, you should use an If() conditional instead. Set analysis is intended when you want the expression to work on a different set than the one in the sheet, but not to make comparisons for each value in the dimension.

Hope that helps.

Miguel

[samvile18]

I think I'm going to have to go with an IF statement then...do you know how I would tell it to ensure the Measure is FUM along with making sure the dates are matching?