Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
I think I would use something like this: ... [Fiscal Year] = {"$(=max([Fiscal Year])-1)"} ...
- Marcus
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)')
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
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?
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
I have the same formula for the current year (selection) data. It works as it should.
Same formula minus the year criteria that is.