4 Replies Latest reply: Apr 14, 2015 5:32 PM by Pedro Lopes RSS

    Count(if(aggr...

    Pedro Lopes

      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

        • Re: Count(if(aggr...
          Ramon Covarrubias

          hi Pedro you can try with this

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

          • Re: Count(if(aggr...
            Onno van Knotsenburg

            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.

              • Re: Count(if(aggr...
                Pedro Lopes

                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;

              • Re: Count(if(aggr...
                Pedro Lopes

                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))