Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
MarioCenteno
Creator III
Creator III

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


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

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?


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

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
Not applicable
Author

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.


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

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