topic Issue with Formula in QlikView

Issue with Formula

paul_hallwood — Tue, 06 Dec 2016 13:59:22 GMT

I'm trying to sum all the activity for a particular chosen financial year, but for some reason I can't get it to work. Here is the formula:

=Sum({$<[FiscalYear]={$(varFiscalYear)}>} [Activity])

varFiscalYear is a variable that evaluates to 2016_17. The error message I get is "Error: Error in set modifier adhoc element list: ',' or ')' expected" but I'm not too sure what the issue is. Any help with this will be much appreciated.

Re: Issue with Formula

sunny_talwar — Tue, 06 Dec 2016 14:00:27 GMT

May be try this:

=Sum({$<[FiscalYear]={$(=varFiscalYear)}>} [Activity])

Re: Issue with Formula

paul_hallwood — Tue, 06 Dec 2016 14:03:45 GMT

No, that hasn't worked unfortunately. Thanks for the speedy response though!

Re: Issue with Formula

paul_hallwood — Tue, 06 Dec 2016 14:17:54 GMT

...if I substitute the varFiscalYear for '2016_17' the formula seems to work. Here is the working formula:

=Sum({$<[FiscalYear]={'2016_17'}>} [Activity])

Would declaring varFiscalYear as a variable and then using it in a formula be causing it any problems at all???

Re: Issue with Formula

sunny_talwar — Tue, 06 Dec 2016 14:19:47 GMT

Try one of these:

=Sum({$<[FiscalYear]={'$(=varFiscalYear)'}>} [Activity])

=Sum({$<[FiscalYear]={'$(=$(varFiscalYear))'}>} [Activity])

Re: Issue with Formula

paul_hallwood — Tue, 06 Dec 2016 14:31:44 GMT

Yes, it seems as though your first formula has done what I have wanted! Brilliant - thank you very much for your responses, they've been much appreciated.

Re: Issue with Formula

sunny_talwar — Tue, 06 Dec 2016 14:36:12 GMT

Brilliant