8 Replies Latest reply: Sep 14, 2018 3:43 AM by guillaume steimer

# Create a KPI from a generated table

Hi All,

I create a table from request and i obtain the table in the attached image

I just want to have as KPI the average of (Tx de marge) if collaborateur present = 1

How can i do that ?

• ###### Re: Create a KPI from a generated table

Try this

Expression dimension collaborateur present

=If(collaborateur present =1, collaborateur present)

Later deactivates the option to include null values in your dimension

• ###### Re: Create a KPI from a generated table

You need to use the aggr function to be able to nest aggregations. Something like this perhaps:

avg({<[Matricule facturation]={"=sum([Collaborateur présents])=1"}>}aggr(sum([Tx de marge]),[Matricule facturation])

• ###### Re: Create a KPI from a generated table

ok, but how can I export this table in the structure of my app.

this table is like a graph… and i can't select the field  [Collaborateur présents] ...

I'm sorry but I'm new on qlik

• ###### Re: Create a KPI from a generated table

What is the expression you used to calculate the measure Collaborateur présents?

• ###### Re: Create a KPI from a generated table

I use this expression :

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

• ###### Re: Create a KPI from a generated table

If i Replace collaborateur présent by his expression, I obtain that and it doesn't work

=avg({<[Matricule facturation]={=sum(\$(Count({<[Début de contrat]={"<=31/08/2018"}>}  [Matricule facturation])-Count({<[Date fin de contrat]={"<=31/08/2018"}>}  [Matricule facturation])))=1}>}aggr(sum([Marge]),[Matricule facturation])

• ###### Re: Create a KPI from a generated table

Try this one:

avg({<[Matricule facturation]={"=Count({<[Début de contrat]={"<=31/08/2018"}>}  [Matricule facturation])-Count({<[Date fin de contrat]={"<=31/08/2018"}>} [Matricule facturation])=1"}>}aggr(sum([Marge]),[Matricule facturation])

And if it doesn't work, please post a small example Qlik Sense app that illustrates the problem.

• ###### Re: Create a KPI from a generated table

the initial table is attached

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