topic Re: Aggr fonction in QlikView

Aggr fonction

marwen_garwachi — Wed, 25 Nov 2020 16:16:04 GMT

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
2015	Amiable	3495	85%
	Judiciare	615
	Total	4110	100%
2014	Amiable	2172	80%
	Judiciare	530
	Total	2702	100%
2013	Amiable	2518	79%
	Judiciare	680
	Total	3198	100%
Total		10010

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

Thanks for help

Re: Aggr fonction

sunny_talwar — Tue, 20 Jun 2017 11:21:51 GMT

Try this

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

Re: Aggr fonction

jonathandienst — Tue, 20 Jun 2017 11:24:35 GMT

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.

Re: Aggr fonction

marwen_garwachi — Tue, 20 Jun 2017 11:26:38 GMT

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 !

Re: Aggr fonction

marwen_garwachi — Tue, 20 Jun 2017 11:30:26 GMT

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...

Re: Aggr fonction

sunny_talwar — Tue, 20 Jun 2017 11:31:12 GMT

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.