using function in set analysis

[#1]

Hi all,

I'm trying to use the year function within set analysis.

Sum({<year([Purchase Creation Date]))>='($(vPrevYear)}>} [Field I want to sum])

Its not recognizing the function for some reason. I cant hook it up to the master calendar year because I need this specific year the master calendar is hooked up to a different field. Is there an easy way to do this without creating a whole separate field on the load script?

Thanks!

[swuehl]

You can only use field names on the left of the equal sign in a set expression, no functions.

Depending on your date format (e.g. MM/DD/YYYY), you can use a search:

Sum({< [Purchase Creation Date] = {"*/$(vPrevYear)"} >} [Field I want to sum])

[vishsaggi]

You cannot write this way in set analysis. Create a Field in your load script it would be easy.

YEAR([Purchase Creation Date]) AS PurYear,

Then use

= Sum({< PurYear = { "=$(>= $(vPrevYear))" } >} [Field I want to sum])

[aarkay29]

in that case you have to use something like this

Sum({<[Purchase Creation Date]={">=$(=AddYears(YearStart(Max([Date])),-1))"}>}[Field I want to sum])

[sunny_talwar]

You can try this too...

Sum({<[Purchase Creation Date] = {"=Year(Only({1}[Purchase Creation Date])) >= $(vPrevYear)"}>} [Field I want to sum])

[swuehl]

I missed the > in your comparison, but you should be able to use it in the search as well:

Sum({< [Purchase Creation Date] = {">='01/01/$(vPrevYear)'"} >} [Field I want to sum])

Adapt to your date format.