Create a KPI from a generated table

[Anonymous]

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 ?

Thanks for your help

[MarioCenteno]

Try this

Expression dimension collaborateur present

=If(collaborateur present =1, collaborateur present)

Later deactivates the option to include null values ​​in your dimension

[Gysbert_Wassenaar]

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

[Anonymous]

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

[Gysbert_Wassenaar]

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

[Anonymous]

I use this expression :

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

[Anonymous]

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

[Gysbert_Wassenaar]

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.

[Anonymous]

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