Someone can help me with this IF conditional expression?

[#1]

Sum

(if (Sum ( {<Fase_id = {'10','11'}>} ((If (Data_fase_retorno < '0' , (TQte ))))) + Sum (Q_estTT) - Sum (QpT_abto ) <=-1)

Just SUM if the expression below was less or equal -1

(Sum ( {<Fase_id = {'10','11'}>} ((If (Data_fase_retorno < '0' , (TQte))))) + Sum (Q_estTT) - Sum (QpT_abto)

[#2]

Use

if(x<=-1,Sum()) with x the expression you posted.

You need to use " instead of ' in set analysis.

And watch the number of '(' compared to the number of ')' 🙂

[stevedark]

Hi there,

I am not quite sure what you are trying to achieve here.

The issue you will have with your first statement is that it is not possible to nest aggregations (and indeed it makes no logical sense to).

The aggr statement may well assist you in what you are after. This returns a record set with values from a expression over specified fields. You need to know which field you are looking for the -1 over.

eg.

=sum(if(aggr(sum(value), field) < -1, aggr(sum(value), field), 0))

You will need to replace sum(value) with your expression in both cases, and field with the field you wish to aggregate over.

If you wish to test out the expression above then use this inline statement in your load:

load * inline [field,valuea,2a,-4b,2b,-1c,5c,-7d,4d,6];

You will see it only totals a and c.

If the -1 you are looking for is on a single row of your data then you need to remove all of the sum statements from your nested expression, and then this can be written without the need for the aggr statement. Given that Set Analysis only works within aggregations you will need to either move this to the outer sum or re-write it to be an if statement.

Hope that helps. Please post back more detail on your issue if you require further help.

Regards,
Steve

[stevedark]

Hi there,

I am not quite sure what you are trying to achieve here.

The issue you will have with your first statement is that it is not possible to nest aggregations (and indeed it makes no logical sense to).

The aggr statement may well assist you in what you are after. This returns a record set with values from a expression over specified fields. You need to know which field you are looking for the -1 over.

eg.

=sum(if(aggr(sum(value), field) < -1, aggr(sum(value), field), 0))

You will need to replace sum(value) with your expression in both cases, and field with the field you wish to aggregate over.

If you wish to test out the expression above then use this inline statement in your load:

load * inline [field,valuea,2a,-4b,2b,-1c,5c,-7d,4d,6];

You will see it only totals a and c.

If the -1 you are looking for is on a single row of your data then you need to remove all of the sum statements from your nested expression, and then this can be written without the need for the aggr statement. Given that Set Analysis only works within aggregations you will need to either move this to the outer sum or re-write it to be an if statement.

Hope that helps. Please post back more detail on your issue if you require further help.

Regards,
Steve

[stevedark]

Apologies, I was getting error messages on posting. This caused me to try and repost causing multiple posts.

Regards,

Steve

[#6]

Dear Steave, thanks for your reply.

I will try to explain better using the information below.

I need to get the sum line just for negative quantities

Regards, Rodrigo

STYLE	COLOR	S	M	L	XL	TOTAL	TOTAL NEGATIVE
T-SHIRT	black	5	5	5	5	20	10		TOTAL is a regular sum
	yellow	6	23	21	-1	49			TOTAL NEGATIVE sum just negative quantities
	blue	-11	-18	-18	-15	-62	-62
	red	-2	-4	12	-1	5	-7		I need to rank the products from the large total negative.
JACKET	black	-9	10	-18	-12	-29	-39
	blue	-11	-21	-22	-13	-67	-67
I'm using the expression below to get the regular sum result						-84	-165
Sum ( {<Fase_id = {'10','11'}>} ((If (Data_fase_retorno < '0' , (Q1))))) + Sum (Q_est1) - Sum (Qp1_abto)
Fase_id	10 and 11 is production steps (in this case transportation from the factory to the warehouse)
Data_fase_retorno	if this date was zero, return the quatity production
Q1	quantity in production (SIZE S)
Q_est1	quantity in stock (SIZE S)
Qp1_abto	quantity in open order (customer) (SIZE S)