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: 
marwen_garwachi
Creator II
Creator II

Aggr fonction

Hello,

I have a problem with aggr fonction :

I need t show the % of dossiers by Doss_Fase per year in a pivot table.

I'm using this Expression : count(distinct Dossier_Id)/aggr(count(distinct Dossier_Id), Dos_CreaYear)

and getting this result :

Dos_CreaYear Dos_FaseDescrFR # Dossiers% Dossiers
2015Amiable349585%
Judiciare615
Total 4110 100%
2014Amiable217280%
Judiciare530
Total 2702 100%
2013Amiable251879%
Judiciare680
Total 3198 100%
Total 10010

Do somone know why it's not showing the Judiciaire ratio ?

Thanks for help

1 Solution

Accepted Solutions
sunny_talwar

Try this

Count(DISTINCT Dossier_Id)/Count(DISTINCT TOTAL <Dos_CreaYear> Dossier_Id)

View solution in original post

5 Replies
sunny_talwar

Try this

Count(DISTINCT Dossier_Id)/Count(DISTINCT TOTAL <Dos_CreaYear> Dossier_Id)

jonathandienst
Partner - Champion III
Partner - Champion III

Because you have not included Dos_FaseDescrFR as a dimension in the Aggr(). But before you add it, I don't think you need an aggr() here. Something like


    Count(distinct Dossier_Id) / Count(distinct total<Dos_CreaYear> Dossier_Id)


should work (correct any field names or syntax errors). If not I, suggest that you post a  sample qvw file with enough sample data and the table excluding the % calculation.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
marwen_garwachi
Creator II
Creator II
Author

Thanks it works ! Dan you explain what was wrong in my expression and what's the right syntaxe to use in this case ?

Thanks in advence !

marwen_garwachi
Creator II
Creator II
Author

Yes it works too...i didn't know that i can use distinct total <dimension>...now i know but i still need to control the syntaxe...

sunny_talwar

What you were seeing was one of the Pitfalls of the Aggr function‌. Specifically, its called grain mis-match... you can address the issue by using NODISTINCT

Count(DISTINCT Dossier_Id)/Aggr(NODISTINCT Count(DISTINCT Dossier_Id), Dos_CreaYear)

Which will ensure that you have value for your denominator for each row (Without NODISTINCT, the value is only populated on just one of the many recurrences of Dos_CreaYear.

We (Me and jontydkpi‌) proposed an alternative, which is much better than using Aggr() to find the total of your count for each Dos_CreaYear using TOTAL function.