Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
but you may use:
{1-<your condition>}
Try this
sum ({$ <dtaApprovazione = {'*'}>}
will return all, except those are null.
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.
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
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)
in set analysis you can also use the functions P() for possible values and E() for exlcuded values
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
Hi Andrea,
when the field is Null set analysis doesn't work.
Use if(IsNull(Field),.....,....)
Antonio
Or try
=SUM({<dtaApprovazione = {'*2014'}>}Amount)
Regards
Alan