# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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)

Pedro Lopes

1 Solution

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

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!
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:

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:

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

Sales2:

net2 / Unit_base_demand as PrecoReal2,
net2 / Unit_base_demand as PrecoSimu2

Resident Sales_temp;
DROP Table Sales_temp;

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