Set Analysis with variable

[#1]

I am attempting to create a set that uses a variable vMaxYear, which is the maximum year. It continuously gives me an error and I can't seem to figure out why

Trying to get Maximum year:

=SUM({<Year([Year&Month]) = {$(=vMaxYear)}>}-Revenue.Sales)

Trying to get Previous year:

=SUM({<Year([Year&Month])= {$(=vMaxYear)-1}>}-Revenue.Sales)

Any help is appreciated. Thank you

[Kushal_Chawda]

try this

Current

=SUM({<Year = {"$(=vMaxYear)"}>} Revenue)

Previous

=SUM({<Year = {"$(=vMaxYear)-1"}>} Revenue)

[marcus_sommer]

The syntax isn't quite correct. You could only filter/compare a field against a dataset and not against an expression. Therefore try something like this:

=SUM({< Year  = {"$(=max(Year))"} >} Revenue.Sales)

and this will be only work if these period-fields are numeric (you couldn't apply a calculation like max() or an comparison like <=> with string-values) and the dataset-format is abolutely identically with the field-format. This meant a match by month from April and 4 will fail within set analysis.

If you haven't these fields as numeric fields within your datamodel I suggest to create them and use them within these calculations.

- Marcus

[Anonymous]

Try Also

=Sum({<YearOrderDateKey={"$(=vVariable-1)"}>} SalesAmount)

of course use your field at the Syntax