Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

7 Replies
marcus_sommer

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

- Marcus

Anonymous
Not applicable
Author

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)')

marcus_sommer

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

Anonymous
Not applicable
Author

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?

marcus_sommer

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

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

Same formula minus the year criteria that is.