3 Replies Latest reply: Apr 25, 2014 9:41 AM by Hervé Vanderheyden

# Sum of contrat in interval

Hello,

I'm in trouble with an expression...

Case:

Products sold by salesman belong's to categories, and these categories are regrouped by family

I must count salesman for each interval (Dimension: Strate vendeur) for earch categories

Intervals are stored in Data Island where "Strate vendeur" is the interval's label field, "Limite inférieure" is the interval's lowest value field  and "Limite supérieure" is the interval's bigest values field. e.g: 1 à 5 --> [1 - 5]

Exemple:

I 've written the following expression "Nombre" : Sum ({\$<Matricule={"=Sum(Nombre)>=\$(=Min([Limite inférieure]))"}> * \$<Matricule={"=Sum(Nombre)<=\$(=Max([Limite supérieure]))"}>} [Compteur vendeur]) , but the expression doesn't works because Min([Limite inférieure]) and Max([Limite supérieure]) return 0 and 999 for each rows.

Field "Compteur vendeur" is my Salesman dimension counter (avoid count(DISTINCT Salesman_id))

Pleas help

Regards

• ###### Re: Sum of contrat in interval

Set analysis expressions calculate one set per chart, not a set per row. You'll have to use if statements to check if a value falls in the interval.

• ###### Re: Sum of contrat in interval

True... I've forgotten that rule.

Now i'm gonna work on if statement.

• ###### Re: Sum of contrat in interval

So i've worked  again on that subject and i'm sharing the solution. If someone have a better practice... please share

sum

(
if (aggr(sum(Nombre), [Regroupement], [Catégorie], [Strate], Matricule) >= [Limite inférieure]
and aggr(sum(Nombre), [Regroupement], [Catégorie], [Strate], Matricule) <= [Limite supérieure]
, 1
, 0
)
)

Thank you