13 Replies Latest reply: Sep 14, 2018 10:31 AM by guillaume steimer

# AVG if

Hi all,

My table is like that and

I want to calculate the average of Tx de marge but I Don't want to take the value if date fin contrat <31/08/2018 and if Matricule facturation = INTERNE

• ###### Re: AVG if

Hi,

try this:

Avg({<[date fin contrat]={">31/08/2018"},[Matricule facturation]-={'INTERNE'}>}Marge)

• ###### Re: AVG if

thanks a lot

• ###### Re: AVG if

you're welcome

good luck

• ###### Re: AVG if

after changement of date, it doesn't work

I think that it's because the formula exclude the empty value in date fin de contrat.

• ###### Re: AVG if

after changement of date, it doesn't work

I think that it's because the formula exclude the value of marge when in date fin de contrat the value is empty

• ###### Re: AVG if

I try this formula without success

Avg({<[Début de contrat]={"<=31/08/2018"},[Date fin de contrat]-={"<31/08/2018"},[Matricule facturation]-={'INTERNE'}>}Marge)

• ###### Re: AVG if

Yes because it is static inside the expression.

you can make it dynamic but how you will select a different date ?

• ###### Re: AVG if

how can make it dynamic ?

I create a variable = 31/08/2018

and when I want to change the date, i Change the value of the variable

• ###### Re: AVG if

you need to put your variable inside the expression, like this:

Avg({<[date fin contrat]={">\$(your_variable_name)"},[Matricule facturation]-={'INTERNE'}>}Marge)

• ###### Re: AVG if

Yes I do it in other expression and that' ok

Avg({<[Date fin de contrat]={">\$(Date de reporting)"},[Matricule facturation]-={'INTERNE'}>}Marge)

But it Always doesn't take the value marge when [date fin de contrat] is empty

I try this one

Avg({<[Début de contrat]={"<=\$(Date de reporting)"},[Date fin de contrat]-={"<\$(Date de reporting)"},[Matricule facturation]-={'INTERNE'}>}Marge)

all the value if [Début de contrat] before (Date_var) and I exclude the value of [date fin de contrat] before (Date Var)

• ###### Re: AVG if

but doesn't work

• ###### Re: AVG if

did your expected output change ?

because in your initial requirement you wasn't mentioning condition on field [Début de contrat]...

• ###### Re: AVG if

no I didn't