Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

gsteimer
New Contributor II

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 ?

Thanks for your helpCapture qlik 2.JPG

8 Replies
guitartrova
Contributor III

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

gsteimer
New Contributor II

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?

gsteimer
New Contributor II

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

gsteimer
New Contributor II

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.

gsteimer
New Contributor II

Re: Create a KPI from a generated table

the initial table is attached

Capture qlik 3.JPG

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