Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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"
You should try to put the $(vDateMax) in quotes like this $("vDateMax")
Did you try putting single quotes around the variable name?
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?
Try
=Num(sum({$<TNA_DATE={'$(vDateMax)'},CONSULTANT_FIRM_ID={*}>} ASSET_ACCOUNT_TNA),'$ #,##0')
would you need to do TNA_DATE = {"$(=vDateMax)"} ? Just adding that equal sign.
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))
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
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')
I find this site extremely helpful when using variables in Set Analysis: