9 Replies Latest reply: Apr 16, 2014 10:27 AM by Alan Farrell

# set analisys

Hello to all

I need via set analisys to calculate the sum of a field only for values of the field where the date approval is valued.

I use the syntax is sum ({\$ <dtaApprovazione = {2014}>}

the syntax is correct but I do not return anything because the field because the field is given in the format dd / mm / yyyy, but by year (dtaApprovazione) = {2014}, it gives me a syntax error.

actually what I should do is to exclude data where the DtaApprovazione is nothing but it seems to me that's set-analisys only works with the equal comparison operator (=) and not different (<>).

Thanks for your cooperation

Andrea

• ###### Re: set analisys

but you may use:

{1-<your condition>}

• ###### Re: set analisys

Try this

sum ({\$ <dtaApprovazione = {'*'}>}

will return all, except those are null.

• ###### Re: set analisys

You can use -=

It will give u a syntax error but will work.

sum(\${< Year -={2014} >} Value)

Will sum Value for all years except 2014.

• ###### Re: set analisys

Hello Andrea,

One of the trouble free ways I like to deal with this type of analysis is by adding an extra field to the table during the load. In the above-mentioned case, you could add the following line of code to the concerned table load as such:

Year(dtaApprovazione) as annoApprovazione,

Then, you will be able to use the following expression to filter on data related to year 2014:

Sum({\$ <annoApprovazione = {2014}>}

Regards,

Philippe

• ###### Re: set analisys

Do you not have Year in your Data model?

If you can add Year in your model,It would be used like

SUM({<Year = {2014}>}Sales)

• ###### Re: set analisys

in set analysis you can also use the functions P() for possible values and E() for exlcuded values

• ###### Re: set analisys

Hi Andrea

In your script you can assign a flag for value of DtaApprovazione as '1' . Then you can use set analysis with '=' or otherwise you can use exclusion function also in set analysis.

e.g. Sum({\$<Year = e({\$<Year = {2014}>})>}Revenue)

Please let us know if this works.

Thanks & Regards

Suhas Gupta

• ###### Re: set analisys

Hi Andrea,

when the field is Null set analysis doesn't work.

Use if(IsNull(Field),.....,....)

Antonio

• ###### Re: set analisys

Or try

=SUM({<dtaApprovazione = {'*2014'}>}Amount)

Regards

Alan