Set Analysis with Variable

[#1]

I have created a variable vDateMax = num(max(TNA_DATE)) to return the max date of date range selected.

I would like a text object to display the sum of ASSETS where TNA_DATE=vDateMax. This is my expression:

=Num(sum({$<TNA_DATE={$(vDateMax)},CONSULTANT_FIRM_ID={*}>} ASSET_ACCOUNT_TNA),'$ #,##0')

Can anyone help; I'm getting "Error in set modifier ad hoc element list: ',' or ')' expected"

[petter]

You should try to put the $(vDateMax) in quotes like this $("vDateMax")

[jpapador]

Did you try putting single quotes around the variable name?

[petter]

and the vDateMax might have to be also quoted ... since I dont know which context have it set....

If it is from the Load Script:   SET vDateMax = Max(TNA_DATE); 

The TNA_DATE is it a purely Numeric field or is it a Date/DateTime field?

[#5]

Try

=Num(sum({$<TNA_DATE={'$(vDateMax)'},CONSULTANT_FIRM_ID={*}>} ASSET_ACCOUNT_TNA),'$ #,##0')

[cspencer3]

would you need to do TNA_DATE = {"$(=vDateMax)"} ? Just adding that equal sign.

[#7]

Thank you all. Adding the quotations solves the error; however, the calculation is still not right. I believe I need to change the variable definition to include the condition (where CONSULTANT_ID exists). Can someone advise on syntax?
vDateMax = num(max(TNA_DATE))

[Colin-Albert]

If the value you are comparing in the set expression is a number then you will not need to use quotes  around the variable   e.g. TNA_DATE={$(vDateMax)}

If the value in TNA_DATE is text, then the variable must be enclosed in single quotes

     TNA_DATE={'$(vDateMax)'}

Dates further complicate this as the date formats must match as well, so you may need to use something like this:

     TNA_DATE={ "$(= date($(vDateMax), 'DD/MM/YYYY')" }

replacing DD//MM/YYYY with your actual date format

[#9]

Thanks for your suggestion, Colin. Now I get "Error in set modifier expression" though

=Num(sum({$<Date(TNA_DATE,'MM/DD/YYYY')={$(vDateMax)},CONSULTANT_FIRM_ID={*}>} ASSET_ACCOUNT_TNA),'$ #,##0')

[cbaqir]

I find this site extremely helpful when using variables in Set Analysis:

http://www.qlikblog.at/1384/set-analysis-wizard-qlikview/