topic Re: Set Analysis with Variable in QlikView

Set Analysis with Variable

Wed, 07 Jan 2015 16:54:44 GMT

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"

Re: Set Analysis with Variable

petter — Wed, 07 Jan 2015 17:08:04 GMT

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

Re: Set Analysis with Variable

jpapador — Wed, 07 Jan 2015 17:08:05 GMT

Did you try putting single quotes around the variable name?

Re: Set Analysis with Variable

petter — Wed, 07 Jan 2015 17:11:33 GMT

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?

Re: Set Analysis with Variable

Wed, 07 Jan 2015 17:13:13 GMT

Try

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

Re: Set Analysis with Variable

cspencer3 — Wed, 07 Jan 2015 19:07:54 GMT

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

Re: Set Analysis with Variable

Wed, 07 Jan 2015 19:21:54 GMT

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

Re: Set Analysis with Variable

Colin-Albert — Wed, 07 Jan 2015 19:28:00 GMT

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

Re: Set Analysis with Variable

Wed, 07 Jan 2015 19:47:20 GMT

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

Re: Set Analysis with Variable

cbaqir — Wed, 07 Jan 2015 21:21:08 GMT

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

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

Re: Set Analysis with Variable

Colin-Albert — Wed, 07 Jan 2015 21:42:17 GMT

Try this, you need to format the variable not the field being compared.

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

Re: Set Analysis with Variable

Thu, 08 Jan 2015 03:04:35 GMT

Figured it out finally. Gave up on using the on-the-fly variable (vDateMax), and used the existing column TNA_DATE instead

=num(Sum({$<CONSULTANT_FIRM_ID={*},TNA_DATE={$(=max(TNA_DATE))}>}ASSET_ACCOUNT_TNA),'$ #,##0')

Re: Set Analysis with Variable

Thu, 08 Jan 2015 03:05:10 GMT

thank you - i already use this site all the time