topic Re: set analisys in QlikView

set analisys

warrentk80 — Wed, 16 Apr 2014 13:04:38 GMT

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

Wed, 16 Apr 2014 13:09:30 GMT

but you may use:

{1-<your condition>}

Re: set analisys

CELAMBARASAN — Wed, 16 Apr 2014 13:12:24 GMT

Try this

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

will return all, except those are null.

Re: set analisys

joachim_boivie — Wed, 16 Apr 2014 13:18:08 GMT

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

pgrenier — Wed, 16 Apr 2014 13:21:48 GMT

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

rustyfishbones — Wed, 16 Apr 2014 13:24:56 GMT

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

mambi — Wed, 16 Apr 2014 13:27:22 GMT

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

Re: set analisys

Wed, 16 Apr 2014 13:32:46 GMT

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

antoniotiman — Wed, 16 Apr 2014 14:15:40 GMT

Hi Andrea,

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

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

Antonio

Re: set analisys

rustyfishbones — Wed, 16 Apr 2014 14:27:10 GMT

Or try

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

Regards

Alan