Count(if(aggr...

[Anonymous]

Hi all,

I need to count all clients where the value of this expression (V1-V2) is negative.

The expression is very complex, but I think my problem is in aggr function.

= COUNT (DISTINCT {<id_cliente = {"aggr = ((sum (PrecoSimu2 * Unit_base_demand)-(sum(aggr(if(sum(PrecoSimu2*Order_Matrix*Unit_base_demand)>CAP,CAP,sum({1}PrecoSimu2*Order_Matrix*Unit_base_demand)),id_master))-(sum(net3))) <-0.01, Id_cliente) "}>} id_cliente)

in short:

= Count (DISTINCT {<id_cliente = {"aggr = ((V1-V2) <-0.01, id_cliente)"}>} id_cliente)

Thanks in advance.

Pedro Lopes

[ramoncova06]

hi Pedro you can try with this

= Count (DISTINCT {<id_cliente = {"=sum(V1-V2)>-0.01"}>} id_cliente)

[oknotsen]

Are you able to edit the script?

If so, it might make your life easier to have (part of) the expression already calculated in the script.

[Anonymous]

I know Onno, and i have some skills to edit the script but the problem is the Fields (Order_Matrix and PrecoSimu2) are calculated fields and inputfield to. If i make some part of this expression in script the program don´t recognizes this two fields. e.g.

INPUTFIELD PrecoSimu2;
INPUTFIELD Order_Matrix;

Sales_temp:
  LOAD Distinct
id_master,
id_cliente,
id_artigo,
valor_bruto,
net1,
net2,
net3,
Unit_base_demand; 
SQL SELECT *
FROM `F_Sales_deloitte`
WHERE Unit_base_demand<>0;

Sales3:
  LOAD Distinct *,

(0 / net3) as Order_Matrix,
(0 / net2) as Loyalty_scheme;

Sales2:
LOAD Distinct *,

net2 / Unit_base_demand as PrecoReal2,
net2 / Unit_base_demand as PrecoSimu2

  Resident Sales_temp;
DROP Table Sales_temp;

[Anonymous · Accepted solution]

Thanks for everthing guys but i find the soluction.

Its more simpler than the first expression:

=count(if(Aggr(sum(PrecoSimu2*Unit_base_demand)-
(sum(aggr(if(sum(PrecoSimu2*Order_Matrix*Unit_base_demand)>CAP,CAP,sum({1}PrecoSimu2*Order_Matrix*Unit_base_demand)),id_master)))-(sum(net3)),id_cliente) <0,id_cliente))