topic Active Selection Year -1 in QlikView

Active Selection Year -1

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

marcus_sommer — Thu, 11 Jan 2018 11:46:53 GMT

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

- Marcus

Re: Active Selection Year -1

Anonymous — Thu, 11 Jan 2018 13:37:10 GMT

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

marcus_sommer — Thu, 11 Jan 2018 13:59:33 GMT

There is a bracket missing:

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

Anonymous — Thu, 11 Jan 2018 20:31:57 GMT

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

marcus_sommer — Thu, 11 Jan 2018 20:47:55 GMT

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

Anonymous — Tue, 16 Jan 2018 17:28:52 GMT

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

Re: Active Selection Year -1

Anonymous — Tue, 16 Jan 2018 17:29:38 GMT

Same formula minus the year criteria that is.