7 Replies Latest reply: Jan 16, 2018 12:29 PM by David Toomey

Active Selection Year -1

Hello,

I have the following formula:

=Num(Sum({<[Distribution Channel] = {'Parts'},[Profit Center Desc] = {'Parts'} + {'Parts Operations'} + {'Selling Expenses'},[Customer Group Desc] -= {'Interdivisional'},

[Short Text] -= {'I/C Com Sales'} + {'Cost of Sales - New'},[Fiscal Year] = {\$-1} >} [Total Revenue]),'\$#,##0;(\$#,##0)')

"Fiscal Year" is currently selected in a List box elsewhere in the sheet and I need a year over year comparison, with this formula presenting the previous year. For the year over year I  attempted "\$ -1" in the sum formula's selection. From my research I thought that the "\$" serves as current selection. I believe I may be wrong. Thoughts?

Thanks,

Dave

• Re: Active Selection Year -1

I think I would use something like this: ... [Fiscal Year] = {"\$(=max([Fiscal Year])-1)"} ...

- Marcus

• Re: Active Selection Year -1

I gave that a shot, however I now have an error: "Error in set modifier ad hoc element list: ',' or ')' expected". The edit expression page still says the expression is "OK". Do you have any idea what this could be?

Here is my current expression:

=Num(Sum({<[Distribution Channel] = {'Parts'},[Profit Center Desc] = {'Parts'} + {'Parts Operations'} + {'Selling Expenses'},[Customer Group Desc] -= {'Interdivisional'},
[Short Text] -= {'I/C Com Sales'} + {'Cost of Sales - New'},[Fiscal Year] = {'\$(=max([Fiscal Year])-1'}>} [Total Revenue]),'\$#,##0;(\$#,##0)')

• Re: Active Selection Year -1

There is a bracket missing:

=Num(Sum({<[Distribution Channel] = {'Parts'},[Profit Center Desc] = {'Parts'} + {'Parts Operations'} + {'Selling Expenses'},[Customer Group Desc] -= {'Interdivisional'},
[Short Text] -= {'I/C Com Sales'} + {'Cost of Sales - New'},[Fiscal Year] = {'\$(=max([Fiscal Year])-1)'}>} [Total Revenue]),'\$#,##0;(\$#,##0)')

Further I used double-quotes around the max-expression. In this case it might not have an impact by using single-quotes but in general there are differences, see: Quotes in Set Analysis

- Marcus

• Re: Active Selection Year -1

Thanks for the response. It's currently pulling 0 as a value. I assume that means its selecting nothing. I know the original part of the formula pulls the right data. Are there any other options?

• Re: Active Selection Year -1

Are you really sure that all other parts work as expected. I suggest to leave the num() part and to reduce the expression to one set condition - if it returned the expected result then add the next condition and so on.

- Marcus

• Re: Active Selection Year -1

I have the same formula for the current year (selection) data. It works as it should.

• Re: Active Selection Year -1

Same formula minus the year criteria that is.