Max() not working in set analysis

[#1]

I am trying to get this formula to work to calculate the prior year's data.

I have a chart that I always want to show the maximum year selected in one column and the prior year in the other column based on the maximum year selected minus 1.

For some reason this keeps ignoring the this portion [Fiscal Year] = {"=$(=max([Fiscal Year])-1)"}  What am I doing wrong?

=Count({<[Fiscal Year] = {"=$(=max([Fiscal Year])-1)"},[Fiscal Year and Month]=,[Fiscal Year and Quarter]= >} distinct [Order Number])

[#2]

Hi debbiepy,

Could you please try with simple quote instead of double quote?

It should work.

Youssouf

[#3]

I tried that and still no luck.

This is a pivot table with two dimensions for Quarter and Year. I get the cumulated data for each year, one for max(FY) and one for Max(FY)-1,

[#4]

I got this to work

{$(=Max(,1))}

{$(=Max(,2))}

[#5]

Normaly you don't need following constraints

[Fiscal Year and Month]=,[Fiscal Year and Quarter]=

What happen when you remove it?

I guest that is applied after the

[Fiscal Year] = {"=$(=max([Fiscal Year])-1)"},

So that it display all Quarters & Months ignoring the first constraint.

[#6]

If the user selects the current Fiscal Year, or other Fiscal Period I want the chart to still populate the prior year regardless. But I want the chart to still be filtered by Partner just not periods.

THe only period filter is the one to determine the maximum so if use selects 2012, the maxium period will show and the other column will contain 2011 data

[#7]

could you please post an application with a sample data?

so that I could see it physically.

Thanks

[#8]

This works. I had to use a differ value to get maximum year so the selection of Fiscal Year did not affect the formula

Current year:

=Count({<[# of FYs from Current] = {'$(=max(,1))'},[Fiscal Year]=,[Fiscal Year and Quarter]=,[Fiscal Year and Month]=>} distinct )

Prior year: Had to use minus 1 because the max(,2) did not work. I am not sure why.

=Count({<[# of FYs from Current] = {'$(=max(,1)-1)'},[Fiscal Year]= ,[Fiscal Year and Quarter]=,[Fiscal Year and Month]=>} distinct )