Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
warrentk80
Creator
Creator

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

9 Replies
Not applicable

but you may use:

{1-<your condition>}

CELAMBARASAN
Partner - Champion
Partner - Champion

Try this

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

will return all, except those are null.

joachim_boivie
Partner - Contributor III
Partner - Contributor III

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.

pgrenier
Partner - Creator III
Partner - Creator III

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

rustyfishbones
Master II
Master II

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)

mambi
Creator III
Creator III

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

Not applicable

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

antoniotiman
Master III
Master III

Hi Andrea,

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

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

Antonio

rustyfishbones
Master II
Master II

Or try

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

Regards

Alan