Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

pedlopes
New Contributor III

Count(if(aggr...

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

1 Solution

Accepted Solutions
pedlopes
New Contributor III

Re: Count(if(aggr...

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

4 Replies
ramoncova06
Valued Contributor III

Re: Count(if(aggr...

hi Pedro you can try with this

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

oknotsen
Honored Contributor III

Re: Count(if(aggr...

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.

May you live in interesting times!
pedlopes
New Contributor III

Re: Count(if(aggr...

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;

pedlopes
New Contributor III

Re: Count(if(aggr...

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

Community Browser